Ygthkb

Was there a pre-determined arrangement for the division of Germany in case it surrendered before any Soviet forces entered its territory?

Players preemptively rolling, even though their rolls are useless or are checking the wrong skills

Can me and my friend spend the summer in Canada (6 weeks) at 16 years old without an adult?

Why do neural networks need so many examples to perform?

Is the symmetric product of an abelian variety a CY variety?

Why did Luke use his left hand to shoot?

How can I differentiate duration vs starting time

If I tried and failed to start my own business, how do I apply for a job without job experience?

Was Claire Dearing blamed for any of Jurassic World's failings?

Is there a non trivial covering of the Klein bottle by the Klein bottle

Isn't a semicolon (';') needed after a function declaration in C++?

Is the percentage symbol a constant?

RS485 using USART or UART port on STM32

Buying a "Used" Router

What is a good way to explain how a character can produce flames from their body?

How can I deduce the power of a capacitor from its datasheet?

Word for something that's always reliable, but never the best?

How can I handle players killing my NPC outside of combat?

How to fly a direct entry holding pattern when approaching from an awkward angle?

Why are mages sometimes played bot instead of traditional ADCs?

Lubuntu 18.10 File Manager: How to view directory tree structure?

Plausible reason for gold-digging ant

Modern Algebraic Geometry and Analytic Number Theory

Rigorous justification for non-relativistic QM perturbation theory assumptions?

Why is this column order in my non-clustered index better for my query?

Does the order of columns in a non-clustered ColumnStore index matter?Why isn't SQL Server using my non-clustered index and doing a clustered index scan?Aligned index column orderWhy SQL Server uses Non-Clustered but not Clustered Index?Does Clustered Index on GUID create more fragmentation than Non Clustered Index?What happens when you delete a Clustered index and Non Clustered Index once createdGetting Max for partition column in clustered columnstore indexUnderstanding non clustered index locking during an updateForcing Flow Distinctcreate a visible uniquifier column for a nonunique clustered index

0

I am working on a query for a table that contains movie tickets. The database holds 380k rows. A rows represents a showing of the movie (which cinema, when, how many tickets and at what price among other things).

I need to compute a few totals for every row: Admissions Paid, Admissions Revenue, Admissions Free and Total Admissions.

For a given row Admissions Paid is the sum of all tickets for that movie up until that point where price>0. The other 3 columns are computed similarly.

I wrote a query and created an index:

 SELECT [ID]

      ,[cinema_name]

      ,[movie_title]

      ,[price]

      ,[quantity]

      ,[start_date_time]

      ,* --I need all the columns for reporting

     ,(select SUM(quantity) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time

        and price=0) as [Admissions Free]

        ,(select SUM(quantity) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time

        and price>0) as [Admissions Paid]

        ,(select SUM(quantity*price) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time

        and price>0) as [Admissions Revenue]

        ,(select SUM(quantity) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time) as [Total Admissions]

  FROM [movies] 

I created the following index which brought the query time down to 5 minutes:

CREATE NONCLUSTERED INDEX [startdatetime_movietitle_price] ON [dbo].[movies]

(

    [movie_title] ASC,

    [start_date_time] ASC,

    [price] DESC

)

INCLUDE (   [quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

But this index brought the query time down to 1:30:

CREATE NONCLUSTERED INDEX [startdatetime_movietitle_price] ON [dbo].[movies]

(

    [start_date_time] ASC,

    [movie_title] ASC,

    [price] DESC

)

INCLUDE (   [quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

So my question is: why? From my understanding it makes more sense to first gather all the movie titles and then look at the start times because there are more start times then there are movies. Distinct movies: 51, distinct start_date_times: 8786

Doesn't the underling B-Tree not cut off more branches if it eliminates the unnecessary start_date_times first?

sql-server-2014

share

asked 5 mins ago

dakesdakes

1

New contributor

dakes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I am working on a query for a table that contains movie tickets. The database holds 380k rows. A rows represents a showing of the movie (which cinema, when, how many tickets and at what price among other things).

I need to compute a few totals for every row: Admissions Paid, Admissions Revenue, Admissions Free and Total Admissions.

For a given row Admissions Paid is the sum of all tickets for that movie up until that point where price>0. The other 3 columns are computed similarly.

I wrote a query and created an index:

 SELECT [ID]

      ,[cinema_name]

      ,[movie_title]

      ,[price]

      ,[quantity]

      ,[start_date_time]

      ,* --I need all the columns for reporting

     ,(select SUM(quantity) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time

        and price=0) as [Admissions Free]

        ,(select SUM(quantity) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time

        and price>0) as [Admissions Paid]

        ,(select SUM(quantity*price) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time

        and price>0) as [Admissions Revenue]

        ,(select SUM(quantity) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time) as [Total Admissions]

  FROM [movies] 

I created the following index which brought the query time down to 5 minutes:

CREATE NONCLUSTERED INDEX [startdatetime_movietitle_price] ON [dbo].[movies]

(

    [movie_title] ASC,

    [start_date_time] ASC,

    [price] DESC

)

INCLUDE (   [quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

But this index brought the query time down to 1:30:

CREATE NONCLUSTERED INDEX [startdatetime_movietitle_price] ON [dbo].[movies]

(

    [start_date_time] ASC,

    [movie_title] ASC,

    [price] DESC

)

INCLUDE (   [quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

So my question is: why? From my understanding it makes more sense to first gather all the movie titles and then look at the start times because there are more start times then there are movies. Distinct movies: 51, distinct start_date_times: 8786

Doesn't the underling B-Tree not cut off more branches if it eliminates the unnecessary start_date_times first?

sql-server-2014

share

asked 5 mins ago

dakesdakes

1

New contributor

dakes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I am working on a query for a table that contains movie tickets. The database holds 380k rows. A rows represents a showing of the movie (which cinema, when, how many tickets and at what price among other things).

I need to compute a few totals for every row: Admissions Paid, Admissions Revenue, Admissions Free and Total Admissions.

For a given row Admissions Paid is the sum of all tickets for that movie up until that point where price>0. The other 3 columns are computed similarly.

I wrote a query and created an index:

 SELECT [ID]

      ,[cinema_name]

      ,[movie_title]

      ,[price]

      ,[quantity]

      ,[start_date_time]

      ,* --I need all the columns for reporting

     ,(select SUM(quantity) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time

        and price=0) as [Admissions Free]

        ,(select SUM(quantity) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time

        and price>0) as [Admissions Paid]

        ,(select SUM(quantity*price) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time

        and price>0) as [Admissions Revenue]

        ,(select SUM(quantity) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time) as [Total Admissions]

  FROM [movies] 

I created the following index which brought the query time down to 5 minutes:

CREATE NONCLUSTERED INDEX [startdatetime_movietitle_price] ON [dbo].[movies]

(

    [movie_title] ASC,

    [start_date_time] ASC,

    [price] DESC

)

INCLUDE (   [quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

But this index brought the query time down to 1:30:

CREATE NONCLUSTERED INDEX [startdatetime_movietitle_price] ON [dbo].[movies]

(

    [start_date_time] ASC,

    [movie_title] ASC,

    [price] DESC

)

INCLUDE (   [quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

So my question is: why? From my understanding it makes more sense to first gather all the movie titles and then look at the start times because there are more start times then there are movies. Distinct movies: 51, distinct start_date_times: 8786

Doesn't the underling B-Tree not cut off more branches if it eliminates the unnecessary start_date_times first?

sql-server-2014

share

asked 5 mins ago

dakesdakes

1

New contributor

dakes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

 SELECT [ID]

      ,[cinema_name]

      ,[movie_title]

      ,[price]

      ,[quantity]

      ,[start_date_time]

      ,* --I need all the columns for reporting

     ,(select SUM(quantity) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time

        and price=0) as [Admissions Free]

        ,(select SUM(quantity) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time

        and price>0) as [Admissions Paid]

        ,(select SUM(quantity*price) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time

        and price>0) as [Admissions Revenue]

        ,(select SUM(quantity) 

        from [movies] i

        where i.movie_title=o.movie_title

        and i.start_date_time<=o.start_date_time) as [Total Admissions]

  FROM [movies] 

CREATE NONCLUSTERED INDEX [startdatetime_movietitle_price] ON [dbo].[movies]

(

    [movie_title] ASC,

    [start_date_time] ASC,

    [price] DESC

)

INCLUDE (   [quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [startdatetime_movietitle_price] ON [dbo].[movies]

(

    [start_date_time] ASC,

    [movie_title] ASC,

    [price] DESC

)

INCLUDE (   [quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

share

asked 5 mins ago

dakesdakes

1

New contributor

dakes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 5 mins ago

dakesdakes

1

New contributor

dakes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 5 mins ago

dakesdakes

1

New contributor

dakes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 5 mins ago

dakesdakes

1