More efficient way

Question

More efficient way

Viewing 15 posts - 16 through 30 (of 43 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Bill_CCAC (10/19/2010)
you folks are all fabulous. thank you all.
I had been trying to use the case statement but ran into problems and didn't get the results I was after. I wasn't grouping with the case statement so with the above advise I added that and I now have it back down to one select and no unions. As you pointed out, "The way it should be" 🙂

How many folds faster is this running now?

Bill_CCAC Valued Member Points: 70 More actions · Answer 2

Bill_CCAC

Valued Member

Points: 70

October 19, 2010 at 2:02 pm

#1239317

9 seconds. 😉

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

It's 9 seconds less?

9 secs total.

It started from 10 secs or 10 days?

Bill_CCAC Valued Member Points: 70 More actions · Answer 4

Bill_CCAC

Valued Member

Points: 70

October 19, 2010 at 2:07 pm

#1239322

9 seconds total execution time.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Bill_CCAC (10/19/2010)
9 seconds total execution time.

How much seconds did you save from the original query compared to the new one.

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 6

very first post, the better part of an hour.

So quite the change for the better.

Greg E

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 7

Bill_CCAC (10/19/2010)
you folks are all fabulous. thank you all.
I had been trying to use the case statement but ran into problems and didn't get the results I was after. I wasn't grouping with the case statement so with the above advise I added that and I now have it back down to one select and no unions. As you pointed out, "The way it should be" 🙂

You might want to post your revised select statement, just in case some other person could learn from the before / after.

Greg E

Bill_CCAC Valued Member Points: 70 More actions · Answer 8

I will but I'm not quite finished with it yet. As soon as I get it sorted I'll posted it.

This turned into a nice post. Thanks all

WayneS SSC Guru Points: 95409 More actions · Answer 9

Bill_CCAC (10/19/2010)
I will but I'm not quite finished with it yet. As soon as I get it sorted I'll posted it.
This turned into a nice post. Thanks all

I'd suggest you post it as it is... you might find that some of the guru's here will be able to spot other things that would make a difference. Especially since someone might actually be willing to look at it now that the code is a lot smaller.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 10

sturner (10/19/2010)
Thanks for making that point GregE.
Just so the original poster doesn't think I'm a grouch and since others did point a few of the more obvious problems, I will add that I see a lot of this stuff in the where clause:
and (DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 18)
You should avoid datediff (and the use of any function) on sargs in the where clause because any indexes on these dates cannot be used. I know this particular case requires looking at each DOB but I just wanted to point that out.

So why not show the right way to do it? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 11

Jeff Moden (10/19/2010)
sturner (10/19/2010)
Thanks for making that point GregE.
Just so the original poster doesn't think I'm a grouch and since others did point a few of the more obvious problems, I will add that I see a lot of this stuff in the where clause:
and (DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 18)
You should avoid datediff (and the use of any function) on sargs in the where clause because any indexes on these dates cannot be used. I know this particular case requires looking at each DOB but I just wanted to point that out.
So why not show the right way to do it? 😉

I might have offered some SQL had the setup scripts been posted.

But like everyone else - stuff is running off the sides of the plate, and more keeps getting piled on top.

And yes - that would be an interesting snippet to see some various solutions for.

Greg E

Bill_CCAC Valued Member Points: 70 More actions · Answer 12

I'm gonna have to talk to the DBA's. It's running around 6 minutes this morning. Up from 9 seconds last night. Here it is. Much better. additional comments welcome if you have any. 🙂

DECLARE @Start_Date As Datetime

DECLARE @End_Date As Datetime

DECLARE @TodaysDate As Datetime

SET @Start_Date = '04/01/2010'

SET @End_Date = '07/01/2010'

SET @TodaysDate = GETDATE()

SELECT COUNT(Clients1.Client_Number) As Totals

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 18 THEN 1 ELSE 0 END) As 'allInAgeGroup0_18'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 19 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 24 THEN 1 ELSE 0 END) As 'allInAgeGroup19-24'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 25 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 29 THEN 1 ELSE 0 END) As 'allInAgeGroup25-29'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 30 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 34 THEN 1 ELSE 0 END) As 'allInAgeGroup30-34'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 35 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 39 THEN 1 ELSE 0 END) As 'allInAgeGroup35-39'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 40 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 44 THEN 1 ELSE 0 END) As 'allInAgeGroup40-44'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 45 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 49 THEN 1 ELSE 0 END) As 'allInAgeGroup45-49'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 50 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 54 THEN 1 ELSE 0 END) As 'allInAgeGroup50-54'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 55 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 59 THEN 1 ELSE 0 END) As 'allInAgeGroup55-59'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 60 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 64 THEN 1 ELSE 0 END) As 'allInAgeGroup60-64'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 65 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 69 THEN 1 ELSE 0 END) As 'allInAgeGroup65-69'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 70 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 74 THEN 1 ELSE 0 END) As 'allInAgeGroup70-74'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 75 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 79 THEN 1 ELSE 0 END) As 'allInAgeGroup75-79'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 80 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 84 THEN 1 ELSE 0 END) As 'allInAgeGroup80-84'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 85 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 89 THEN 1 ELSE 0 END) As 'allInAgeGroup85-89'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 90 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 94 THEN 1 ELSE 0 END) As 'allInAgeGroup90-94'

,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 95 THEN 1 ELSE 0 END) As 'allInAgeGroup95+'

, Referrals1.Site_Code, Referrals1.Site_Code_Description, DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) As 1stHmVisitTime

FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN

dbo.AdHoc_Clients AS Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN

dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)

--AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)

--AND (104/*Referrals.Site_Code*/ = Referrals1.Site_Code)

AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN

dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN

dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)

ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)

AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN

dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN

dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)

ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)

GROUP BY Referrals1.Site_Code, Referrals1.Site_Code_Description

Go

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

How long did it take before you started using our recommendations?

Bill_CCAC Valued Member Points: 70 More actions · Answer 14

Bill_CCAC

Valued Member

Points: 70

October 20, 2010 at 7:35 am

#1239685

over an hour

irobertson Hall of Fame Points: 3695 More actions · Answer 15

Hi Bill,

One thing you might like to try is to pre-calculate the two correlated subqueries that you're using to generate criteria and hold the values in variables.

Also, a comment on readability - using aliases for table names can make things a bit easier to read (and type :-)), e.g.

instead of

select xyz

from MyLongTableName

join MyOtherLongTableName

on MyLongTableName.MyJoinField = MyOtherLongTableName.MyJoinField

try

select xyz

from MyLongTableName mlt

join MyOtherLongTableName molt

on mlt.MyJoinField = molt.MyJoinField