[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Multiple t select statements on single table into single Optimal query , SQL Server 2008

Posted on 2011-05-06
5
Medium Priority
?
374 Views
Last Modified: 2012-05-11
Hi,
I have a table ..say 'tbl_Profit' with 2 million records.
trying to pull up a value, Using three select query's on same table for different conditions .
(pls take a look at Query's inside code block)

Note:- Index's on the 'tbl_Profit' table
                   Account_number -> PrimaryKey
                  But_date     -> Non-Clustered Index

Question:
1) will each select statement run through all 2million records?
2) Is there a possible way to combine all the three select Statements into one Optimal query...thus reducing execution time.

Thank you
select @daily_div= sum(p.credit) from tbl_profit p where
 p.account_number = @Account and p.cusip = @Cusip 
   and p.entry in ( 'AAA', 'BBB', 'CCC' )
   and p.Buy_date >= @recent 
   
select @mtd_dividends= sum(p.credit) from tbl_profit p where
 p.account_number = @Account and p.cusip = @Cusip 
   and p.entry in ( 'AAA', 'BBB', 'CCC' )
   and p.Buy_date >= @month_begin 
   
select @ytd_dividends= sum(p.credit) from tbl_profit p where
 p.account_number = @Account and p.cusip = @Cusip 
   and p.entry in ( 'AAA', 'BBB', 'CCC' )
   and p.Buy_date >= @year_begin 
--------------------------------------------------------------

Open in new window

0
Comment
Question by:kishan66
  • 2
  • 2
5 Comments
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 600 total points
ID: 35707429
You can combine all the queries like this. But the check the performance over three individual queries.
SELECT @daily_div = SUM(CASE 
                          WHEN p.Buy_date >= @recent THEN p.credit 
                          ELSE 0 
                        END), 
       @mtd_dividends = SUM(CASE 
                              WHEN p.Buy_date >= @month_begin THEN p.credit 
                              ELSE 0 
                            END), 
       @ytd_dividends = SUM(CASE 
                              WHEN p.Buy_date >= @year_begin THEN p.credit 
                              ELSE 0 
                            END) 
  FROM tbl_profit p 
 WHERE p.account_number = @Account 
       AND p.cusip = @Cusip 
       AND p.entry IN ('AAA','BBB','CCC') 

Open in new window

0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 1400 total points
ID: 35707481
Question:
1) will each select statement run through all 2million records?

Short answer, "Yes."
Long answer: It kind of depends on what indexes you have on the table and a few other things.  

For instance, consider the following,
SELECT @mtd_dividends = SUM(p.credit)
FROM tbl_profit p
WHERE p.account_number = @Account
    AND p.cusip = @Cusip
    AND p.entry IN('AAA'
                 ,'BBB'
                 ,'CCC')
    AND p.Buy_date >= @month_begin;

Open in new window


If you have an index on account_number and cusip, your query's performance might not be all that bad because it will use that index and, thus, limit the number of rows that it processes.

you might also want to slightly restructure (see below) especially if you have included the Buy_date as part of your index that has the account_number and cusid.

SELECT @mtd_dividends = SUM(p.credit)
FROM tbl_profit p
WHERE p.account_number = @Account
    AND p.cusip = @Cusip
    AND p.Buy_date >= @month_begin;
    AND p.entry IN('AAA'
                 ,'BBB'
                 ,'CCC')

Open in new window


Essentially, I would make sure that your WHERE clause matches your index's columns, including the order of the columns.

2) Is there a possible way to combine all the three select Statements into one Optimal query...thus reducing execution time.
Well, that kind of depends on how you are using them and whether you can handle the results that would be returned; however, I would suggest that you would be better off tunigng and executing each individual query rather than trying to create one Optimal query.  The results from the one Optimal query would be rather complicated to deal with.
0
 

Author Comment

by:kishan66
ID: 35707619
Thanks Sharth & 8080 Diver for your comments.

Sharth i would definitely try your code and check the execution time .

8080 Diver,
I am clear with my Q1 now. Thank you.
but Question 2 , why did u say "it depends" .
- Isn't that obvious using single query on a table with index's)  will for sure reduce the number of loops when compared to Multiple query's?
(pls help me understand the factors which made you think so...So that it might help me in future)

Note:-  i mentioned i have index's created on the Table (pls refer to my main question)

0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 1400 total points
ID: 35708309
Note:-  i mentioned i have index's created on the Table (pls refer to my main question)

Yes . . . however, as I pointed out, your constraints don't exactly match your indexes.  That can be a problem with large tables.  Also, since the query engine is probably going to wind up using the Primary Key, having a "covering index" with the pertinent columns could well improve your queries performance because it could reduce the I/O due to not having to carry the full row's width and, instead only having the minimal width required for the specific columns involved.

will for sure reduce the number of loops
The question of the "loops" may be one of application performance (or are you using cursors in a stored procedure).  Based on your not having the Buy_date involved in any of your indexes combined with the values you pass in to the 3 parameters, There is no guarantee as to how many rows would need to be processed for each of the queries; however, the combined query, as presented, guarantees that you will process every row in the table.  Under many circumstances, the 3 parameters might result in your individual queries processing a considerably smaller percentage of the table's rows.  If you run 3 queries that each process 10% of the rows, I sort of think that the execution time might be better than one query that does a full table scan on a 2 million row table. ;-)
0
 

Author Comment

by:kishan66
ID: 35708379
That's a very good explanation..... Mr. 8080 Driver.

Thanks again for clarifying the doubts.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question