Query optimization help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query optimization help

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-03-23 : 15:21:25

I have table like below with the respective order of Primary keys
SegmentDateTime,InsuranceID,CollectorID,ActTypeID,BillingID.

SegmentDateTime InsuranceId CollectorID ActTypeID BillingID Balance
12/31/04 0:00 SP SP COLL I INP 108 -54
12/31/04 0:00 SP SP COLL I INP 262 -262
12/31/04 0:00 SP SP COLL ZIPCATCH 104 -108
1/31/05 0:00 AARP COLL 2 I INP 109 876
1/31/05 0:00 AARP COLL 2 I INP 142 875.99
1/31/05 0:00 AARP COLL 3 I INP 142 850
1/31/05 0:00 AARP COLL 2 O OUT 1431 32.76
1/31/05 0:00 AARP COLL 2 O OUT 2460 2082
1/31/05 0:00 AARP COLL 3 O OUT 2460 165.24
1/31/05 0:00 AARP COLL 2 O OUT 2884 261.61
1/31/05 0:00 AARP COLL 2 O OUT 3002 1333.12
1/31/05 0:00 AARP COLL 2 O OUT 3227 74.86
1/31/05 0:00 AARP COLL 2 O OUT 712 71.5
1/31/05 0:00 AARP COLL 2 O OUT 722 52.97

I would like the query to do the function like this but current one takes a long time to run the query, so any optimized code is greatly appreciated.

select BillingID,Balance,AccountTypeID
from Tblx
where A.SegmentDateTime = '1/31/2010' and
A.BillingID IN (select Distinct BillingID from Tblx where SegmentDateTime = '12/31/09' group by BillingID Having sum(Balance)<0)

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-03-23 : 18:46:02

try a corelated subquery:


select A.BillingID,Balance,AccountTypeID
from Tblx A
Join (select Distinct BillingID 
	from Tblx 
	where SegmentDateTime = '......' 
	group by BillingID 
	Having sum(Balance)<0) B On B.BillingID = A. BillingId
where A.SegmentDateTime = '.........'

and create a non-clustered index on BillingId.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-23 : 19:00:17

Just a side note: It's a good idea to use the date format yyyy-mm-dd when hard coding dates. This is because not all countries use the mm-dd-yyyy format. (In fact, I can see why anyone uses it. Surely it makes more sense to sort the parts of the date based on their significance).

For example, the date '02/01/09' will represent Feb 1 2009 in the US, but in Australia it will represent Jan 2 2009. However, the date '2009-02-01' will represent Feb 1 2009 in both countries.

There are 10 types of people in the world, those that understand binary, and those that don't.

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 04:01:13

"It's a good idea to use the date format yyyy-mm-dd when hard coding dates."

Note that it needs to be yyyymmdd - without any hyphens.

(or "yyyy-mm-ddThh:mm:ss.sss" if you want to put time in too)

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-03-24 : 09:29:41

Thanks Dinakar it ran real fast.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-24 : 10:46:25

quote:
Note that it needs to be yyyymmdd - without any hyphens

Sorry Kristen but this is not correct. ISO 8601 states "Calendar date is the most common date representation. It is: YYYY-MM-DD"

-> http://www.iso.org/iso/date_and_time_format#what-iso-8601-covers

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-24 : 10:52:47

ISO 8601: Representations can be in one of two formats: a basic format that has a minimal number of characters and an extended format that adds characters to enhance human readability. For example, the third of January 2003 can be represented as either 20030103 or 2003-01-03

This a part of text coming from your link Lumbago

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 12:53:55

" Sorry Kristen but this is not correct. ISO 8601 states"

That may be what ISO states, but SQL will not parse "9999-99-99" format dates unambiguously, they are subject to locale rules and thus may not deliver as expected depending on server settings and SET LANGUAGE within SQL Server.


SET LANGUAGE US_English
SELECT CONVERT(datetime, '2010-01-02')	-- 2010-01-02
SELECT CONVERT(datetime, '20100102')	-- 2010-01-02

SET LANGUAGE British
SELECT CONVERT(datetime, '2010-01-02')	-- 2010-02-01 !!!
SELECT CONVERT(datetime, '20100102')	-- 2010-01-02

SET dateformat DMY
SELECT CONVERT(datetime, '2010-01-02')	-- 2010-02-01
SELECT CONVERT(datetime, '20100102')	-- 2010-01-02
SELECT CONVERT(datetime, '10-01-02')	-- 2002-01-10

SET dateformat MDY
SELECT CONVERT(datetime, '2010-01-02')	-- 2010-01-02
SELECT CONVERT(datetime, '20100102')	-- 2010-01-02
SELECT CONVERT(datetime, '10-01-02')	-- 2002-10-01

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 13:07:18

(I added the "99-99-99" examples as I think that is another weakness of hyphen-style - someone may accidentally leave out the century, and then it becomes a very different animal!)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-25 : 03:04:00

Holy smokes Kristen, my sincere apologies! This one totally blew me away:

SET LANGUAGE British
SELECT CONVERT(datetime, '2010-01-02')	-- 2010-02-01 !!!

I'll think (at least) twice before questioning you again

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-25 : 03:12:18

I've actually considered the YYYY-MM-DD format to be the only safe choice for almost a decade! If there are any other obvious facts I've missed about anything please tell me asap

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 04:27:08

"I'll think (at least) twice before questioning you again"

Hey, I'm as fallible as the next guy .... don't worry on that score, and please question everything (but I will claim it was a typo

)

I cannot understand why Language = British takes "2010-01-02" and YDM ... I can only think that the logic is looking for D-M-Y, Looks for Day, finds a "big" number, allocates it to Year, and then is still looking for Day ...

Luckily when I first started in SQL we had a client server that gave error when we used yyyy-mm-dd, otherwise I reckon I would have thought that that was failsafe too.

SQL tried to parse any old rubbish as a date, and I think there is far too much flexibility in the algorithm - "enough rope to hang yourself" - its hard to find the info in BoL too

In BoL under "Using Date and Time Data" section: "String Literal Date and Time Formats" it says that "Un-separated ISO 8601" (e.g. '19980223') is not DATEFORMAT dependant. OK.

It also says that "ISO 8601 Numeric" (e.g. '1998-02-23') is not date format dependant. Either this is plain wrong, or I an misunderstanding what they mean.


SET DATEFORMAT DMY
SELECT	CONVERT(datetime, '1998-02-23')

gives me error. Although converting to "datetime2" (SQL 2008) behaves correctly


SET LANGUAGE British
SELECT CONVERT(datetime2, '2010-01-02')

works correctly (whereas, as shown above, converting to "datetime" does not)

On ASNI SQL Standard format (e.g. '1998-02-23 14:23:05') BoL specifically indicates that this is only DATEFORMAT independent for datetime2

I expect that most servers are set to US Locale (pretty much all the ones I come across in the UK are) which may hide the problem.

But the difference in behaviour for datetime2 may cause some annoyances!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources