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.
Author |
Topic |
subhaoviya
Posting Yak Master
135 Posts |
Posted - 2010-12-29 : 00:57:11
|
Hi friends, please help me to optimize the below mentioned query.SELECT SUM([SwitchAccepted]) AS [CORNSaccepted] FROM table1 [summ] inner join ( SELECT COUNT(DISTINCT([DeliveredFileName])) AS [OutputFiles], [CD].[DateHourStamp] AS [DateStamp] FROM table2 [CDF] INNER JOIN table3 [CD] ON [CDF].[ClearinghouseClaimIdentifier] = [CD].[ClearinghouseClaimIdentifier] WHERE DATEDIFF(DAY,0,[CD].[DateHourStamp]),0) AND [CD].[ProcessingClearinghouseIdentifier]='OK' GROUP BY [CD].[DateHourStamp] )[res] on DATEADD(DAY,DATEDIFF(DAY,0,[res].[DateHourStamp]),0)=DATEADD(DAY,DATEDIFF(DAY,0,[summ].[DateHourStamp]),0) WHERE [summ].[ClearinghouseIdentifier]='OK' GROUP BY [summ].[DateStamp],[res].[OutputFiles] thanks a lot in advanceby subha |
|
Sachin.Nand
2937 Posts |
Posted - 2010-12-29 : 01:29:46
|
Do you have any indexes on the tables in question that are used in the query ?PBUH |
|
|
subhaoviya
Posting Yak Master
135 Posts |
Posted - 2010-12-29 : 03:36:04
|
Ya we have indexes.the thing here is the index in one table not been used as fk in other table.am the query is too slow - reasonDATEADD(DAY,DATEDIFF(DAY,0,[res].[DateHourStamp]),0)=DATEADD(DAY,DATEDIFF(DAY,0,[summ].[DateHourStamp]),0)datetime stamp used in table3,and table1. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-12-29 : 03:43:12
|
A index on a column will not be used if there is function wrapped around the column.PBUH |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-29 : 03:50:02
|
>> DATEADD(DAY,DATEDIFF(DAY,0,[res].[DateHourStamp]),0)=DATEADD(DAY,DATEDIFF(DAY,0,[summ].[DateHourStamp]),0)This is just getting rows where the dateties are the same day.If you need to do this sort of thing you should hold the day separately so that you don't have to convert.Until you can redesign it might help to just use the function on one table so it can use an index on the other.[res].[DateHourStamp]) >= DATEADD(DAY,DATEDIFF(DAY,0,[summ].[DateHourStamp]),0)and [res].[DateHourStamp]) < DATEADD(DAY,DATEDIFF(DAY,0,[summ].[DateHourStamp]),0) + 1==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-12-29 : 04:25:23
|
Just a simple addition to the where clause will force the optmizer to use a seek [res].[DateHourStamp])>'1-1-1900' and [summ].[DateHourStamp]>'1-1-1900'But that wont make the query faster if the no of logical reads done by the index seek and the scans are same.PBUH |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-29 : 04:34:38
|
quote: Originally posted by Sachin.Nand Just a simple addition to the where clause will force the optmizer to use a seek [res].[DateHourStamp])>'1-1-1900' and [summ].[DateHourStamp]>'1-1-1900'But that wont make the query faster if the no of logical reads done by the index seek and the scans are same.PBUH
Would be very surprised if that helped at all. Only if there is a lot of data before 19000101 and then it would give incorrect results if that data was required.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-12-29 : 04:39:44
|
I know it wont help.It is just to "force" the optimizer to use a seek.PBUH |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-12-29 : 06:01:55
|
quote: Originally posted by nigelrivett >> DATEADD(DAY,DATEDIFF(DAY,0,[res].[DateHourStamp]),0)=DATEADD(DAY,DATEDIFF(DAY,0,[summ].[DateHourStamp]),0)This is just getting rows where the dateties are the same day.If you need to do this sort of thing you should hold the day separately so that you don't have to convert.Until you can redesign it might help to just use the function on one table so it can use an index on the other.[res].[DateHourStamp]) >= DATEADD(DAY,DATEDIFF(DAY,0,[summ].[DateHourStamp]),0)and [res].[DateHourStamp]) < DATEADD(DAY,DATEDIFF(DAY,0,[summ].[DateHourStamp]),0) + 1==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
Sorry I take my word back.I just tested it on my box with a million records.My seek predicate which I posted earlier performed the worst.Also but are you sure an index on [summ].[DateHourStamp] will be used because already you are using a function on it[res].[DateHourStamp]) >= DATEADD(DAY,DATEDIFF(DAY,0,[summ].[DateHourStamp]),0)PBUH |
|
|
|
|
|
|
|