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
 General SQL Server Forums
 New to SQL Server Programming
 query optimization

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 advance
by 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

Go to Top of Page

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 - reason
DATEADD(DAY,DATEDIFF(DAY,0,[res].[DateHourStamp]),0)=DATEADD(DAY,DATEDIFF(DAY,0,[summ].[DateHourStamp]),0)

datetime stamp used in table3,and table1.
Go to Top of Page

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

Go to Top of Page

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.
Go to Top of Page

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

Go to Top of Page

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.
Go to Top of Page

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

Go to Top of Page

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

Go to Top of Page
   

- Advertisement -