Query Slow  
Author Message
tedsullivan





PostPosted: Fri May 07 15:15:13 CDT 2004 Top

SQL Server Developer >> Query Slow

Hi I have a query which returns 9000 rows in 6 minutes, the problem is with the select top 1 statement which hits the atstransacts table having 14 million rows, if the order by is removed from the select top 1 query the overall query returns rows in 5 sec. any suggestion to change the query or to improve performance

SELECT TOP 100 PERCEN

dbo.AtsPerss.intFundIdNo,

(SELECT top 1 dtstransactdat
FROM atstransacts
WHERE atsrefrequests.guifundeventid = A.guifundeventid
AND A.dtsfundeddate IS NOT NULL
AND atsRefRequestDetails.chraccttype = A.chraccttype
AND substring(A.chrtransacttype, 3, 2) <> 'IN
ORDER BY dtstransactdate DESC )
AS PaidToDate,

(SELECT top 1 dtstransactdat
FROM atstransacts
WHERE atsrefrequests.guifundeventid = A.guifundeventid
AND A.dtsfundeddate IS NOT NULL
AND atsRefRequestDetails.chraccttype = A.chraccttype
AND substring(A.chrtransacttype, 3, 2) = 'IN
ORDER BY dtstransactdate DESC )
AS CreditedToDate,

dbo.atsRefRequestDetails.chrAcctType
dbo.atsRefRequests.dtmRequestDate

FROM dbo.atsRefRequests
INNER JOIN dbo.atsRefRequestDetails
ON dbo.atsRefRequests.guiUniqueid = dbo.atsRefRequestDetails.guiRefRequestsID
INNER JOIN dbo.AtsPerss
ON dbo.atsRefRequests.guiPersId = dbo.AtsPerss.guiUniqueID
INNER JOIN dbo.atsMetaAcctBreakdownTypes
ON dbo.atsRefRequestDetails.chrAcctBreakdownType = dbo.atsMetaAcctBreakdownTypes.chrAcctBreakdownType
LEFT OUTER JOIN dbo.AtsAddrHist
ON dbo.atsRefRequests.intAddressID = dbo.AtsAddrHist.intaddressi

ORDER BY dbo.atsRefRequestDetails.intSortOrde

SQL Server233  
 
 
Jami





PostPosted: Fri May 07 15:15:13 CDT 2004 Top

SQL Server Developer >> Query Slow It would be interesting to see the qeury execution plan for this.

Do you have an index on atstransacts for dtstransactdate? If not, you'll definitely need it.
Otherwise, try sending the execution plan from Query Analyzer so we can see what it is doing. The
plan for the select top 1... statement would help.

BTW, did the suggestion to us MIN (or MAX in this case, I think) help?

Thanks,

Jami



>Hi I have a query which returns 9000 rows in 6 minutes, the problem is with the select top 1 statement which hits the atstransacts table having 14 million rows, if the order by is removed from the select top 1 query the overall query returns rows in 5 sec. any suggestion to change the query or to improve performance.
>
>SELECT TOP 100 PERCENT
>
> dbo.AtsPerss.intFundIdNo,
>
> (SELECT top 1 dtstransactdate
> FROM atstransacts A
> WHERE atsrefrequests.guifundeventid = A.guifundeventid
> AND A.dtsfundeddate IS NOT NULL
> AND atsRefRequestDetails.chraccttype = A.chraccttype
> AND substring(A.chrtransacttype, 3, 2) <> 'IN'
> ORDER BY dtstransactdate DESC )
> AS PaidToDate,
>
> (SELECT top 1 dtstransactdate
> FROM atstransacts A
> WHERE atsrefrequests.guifundeventid = A.guifundeventid
> AND A.dtsfundeddate IS NOT NULL
> AND atsRefRequestDetails.chraccttype = A.chraccttype
> AND substring(A.chrtransacttype, 3, 2) = 'IN'
> ORDER BY dtstransactdate DESC )
> AS CreditedToDate,
>
> dbo.atsRefRequestDetails.chrAcctType,
> dbo.atsRefRequests.dtmRequestDate
>
>FROM dbo.atsRefRequests
> INNER JOIN dbo.atsRefRequestDetails
> ON dbo.atsRefRequests.guiUniqueid = dbo.atsRefRequestDetails.guiRefRequestsID
> INNER JOIN dbo.AtsPerss
> ON dbo.atsRefRequests.guiPersId = dbo.AtsPerss.guiUniqueID
> INNER JOIN dbo.atsMetaAcctBreakdownTypes
> ON dbo.atsRefRequestDetails.chrAcctBreakdownType = dbo.atsMetaAcctBreakdownTypes.chrAcctBreakdownType
> LEFT OUTER JOIN dbo.AtsAddrHist
> ON dbo.atsRefRequests.intAddressID = dbo.AtsAddrHist.intaddressid
>
>ORDER BY dbo.atsRefRequestDetails.intSortOrder
>
>

 
 
anonymous





PostPosted: Fri May 07 15:36:02 CDT 2004 Top

SQL Server Developer >> Query Slow I used max but its not giving a performance boost, also there is a non clustered index on this column. If the order by clause is removed the query is pretty fast, I guess its just because it has to accumulate 14 million rows and then get the top 1 row, as for query execution plan it shows overhead on bookmark lookup. any suggestion on using alternatives like view or something else

Rodger
 
 
Jami





PostPosted: Fri May 07 16:19:27 CDT 2004 Top

SQL Server Developer >> Query Slow Could be - 14M rows at the end of the query will take a while to process. Is there any way you can
reduce the set? For example, if you knew that there had to be a record in the last month...

Another possibility would be to keep "older" records in a history table so you keep your main table
smaller. You can create a view that joins the current and history when you need to see it all.

Sorry I'm not more help.

Good luck,

Jami



>I used max but its not giving a performance boost, also there is a non clustered index on this column. If the order by clause is removed the query is pretty fast, I guess its just because it has to accumulate 14 million rows and then get the top 1 row, as for query execution plan it shows overhead on bookmark lookup. any suggestion on using alternatives like view or something else.
>
>Rodger

 
 
Jaxon





PostPosted: Fri May 07 16:51:03 CDT 2004 Top

SQL Server Developer >> Query Slow can you place a Clustered Index on the Order By Column ?


Greg Jackson
PDX, Oregon


 
 
Gert-Jan





PostPosted: Sat May 08 18:34:57 CDT 2004 Top

SQL Server Developer >> Query Slow Let's start with some questions:
1. Why are you using TOP 100 PERCENT? There is no need for it in this
query, so it is better to lose it.
2. Your atstransacts table does not seem to be normalized. Otherwise you
would not have filter out the 3rd and 4th character out of the
chrtransacttype column. SQL-Server will never be able to index such a
column or make meaningful predictions about its distribution and
selectivity. In other words, it may hurt performance
3. Why are you left joining with table AtsAddrHist when you don't seem
to be using any columns from this table?
4. Is the join with the atsMetaAcctBreakdownTypes table necessary? If
not, lose it.

Then a few suggestions. Since you did not post any DDL, it can only be
guesses and tips in general:
- Normalize all tables
- Make sure all tables have a primary key, and that all foreign key
constraints are defined
- Make sure you indexed all relevant foreign key constraints
- Pay special attention to the indexing of table atstransacts
- Use the clustered index where it is helpfull
- Replace TOP 1 .. ORDER BY DESC with MAX (without ORDER BY)
- Try creating a clustered index on atstransacts(dtstransactdate) to see
if it helps
- Try the Index Tuning Wizard for suggestions with respect to indexing

Hope this helps,
Gert-Jan



>
> Hi I have a query which returns 9000 rows in 6 minutes, the problem is with the select top 1 statement which hits the atstransacts table having 14 million rows, if the order by is removed from the select top 1 query the overall query returns rows in 5 sec. any suggestion to change the query or to improve performance.
>
> SELECT TOP 100 PERCENT
>
> dbo.AtsPerss.intFundIdNo,
>
> (SELECT top 1 dtstransactdate
> FROM atstransacts A
> WHERE atsrefrequests.guifundeventid = A.guifundeventid
> AND A.dtsfundeddate IS NOT NULL
> AND atsRefRequestDetails.chraccttype = A.chraccttype
> AND substring(A.chrtransacttype, 3, 2) <> 'IN'
> ORDER BY dtstransactdate DESC )
> AS PaidToDate,
>
> (SELECT top 1 dtstransactdate
> FROM atstransacts A
> WHERE atsrefrequests.guifundeventid = A.guifundeventid
> AND A.dtsfundeddate IS NOT NULL
> AND atsRefRequestDetails.chraccttype = A.chraccttype
> AND substring(A.chrtransacttype, 3, 2) = 'IN'
> ORDER BY dtstransactdate DESC )
> AS CreditedToDate,
>
> dbo.atsRefRequestDetails.chrAcctType,
> dbo.atsRefRequests.dtmRequestDate
>
> FROM dbo.atsRefRequests
> INNER JOIN dbo.atsRefRequestDetails
> ON dbo.atsRefRequests.guiUniqueid = dbo.atsRefRequestDetails.guiRefRequestsID
> INNER JOIN dbo.AtsPerss
> ON dbo.atsRefRequests.guiPersId = dbo.AtsPerss.guiUniqueID
> INNER JOIN dbo.atsMetaAcctBreakdownTypes
> ON dbo.atsRefRequestDetails.chrAcctBreakdownType = dbo.atsMetaAcctBreakdownTypes.chrAcctBreakdownType
> LEFT OUTER JOIN dbo.AtsAddrHist
> ON dbo.atsRefRequests.intAddressID = dbo.AtsAddrHist.intaddressid
>
> ORDER BY dbo.atsRefRequestDetails.intSortOrder

--
(Please reply only to the newsgroup)