Query is running very, very slowly

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 is running very, very slowly

Author

Topic

knichols
Starting Member

12 Posts

Posted - 2014-06-25 : 16:50:33

I know there is something wrong in this sql code.

Can someone shed some light for me?

[code]
F OBJECT_ID('tempdb..#kurt') IS NOT NULL
BEGIN
DROP TABLE #kurt
END
SELECT * INTO #kurt
FROM XL_USAL_GENERAL...Honda_Unapplied$ as x
DELETE
FROM #kurt WHERE COMPANY IS NULL
USE [FIT_TMWGeneral]
SELECT DISTINCT pa.vin, pa.service_cd, pa.amount, pa.ord_hdrnumber, Sum(pa.amount), honunapp.VIN, honunapp.RECEIPTAMOUNT, ua.CA_Status, ua.ARP_Status, ua.Trx_Number, ua.Line_Number, ua.Revenue_Amount, ua.Order_Number
FROM [USAL_ArOpen] ua
--INNER JOIN [USAL_PaymentAcks] pa
LEFT JOIN [USAL_PaymentAcks] pa ON ua.Vin = pa.VIN
LEFT JOIN [USAL_ArOpen] ON ua.Vin = pa.VIN
LEFT JOIN #kurt honunapp ON honunapp.VIN = pa.VIN
AND pa.VIN = ua.Vin
--AND honunapp.VIN = pa.VIN
AND pa.status_code = 'Y'
AND pa.service_cd = ua.Line_Type
AND ua.End_Date IS NULL
AND pa.ord_hdrnumber = ua.Order_Number
--AND Vin.[USAL_ArOpen] ua = Vin.[USAL_PaymentAcks] pa
WHERE pa.ord_hdrnumber = ua.Order_Number
--VIN.[USAL_ArOpen] ua = Vin.[USAL_PaymentAcks] pa
GROUP BY pa.vin, pa.service_cd, pa.amount, pa.ord_hdrnumber, honunapp.VIN, honunapp.RECEIPTAMOUNT, ua.CA_Status, ua.ARP_Status, UA.Trx_Number, ua.Line_Number, ua.Revenue_Amount, ua.Order_Number
[/code/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-25 : 17:05:53

Add SET STATISTICS IO ON to your query window and then run the whole thing. Post the output of the stats io.

You likely are missing indexes. The stats io output will help zero in on which tables to target first.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-25 : 17:12:09

Also, you WHERE clause is, effectively, making some of the outer joins an inner join, so you might want to change your join types or adjust your WHERE clause.

knichols
Starting Member

12 Posts

Posted - 2014-06-25 : 17:46:35

Here is the new code:

SELECT *
FROM XL_USAL_GENERAL...Honda_Unapplied$
USE [FIT_TMWGeneral]
SELECT DISTINCT pa.vin, pa.service_cd, pa.amount, pa.ord_hdrnumber, Sum(pa.amount), honunapp.VIN, honunapp.RECEIPTAMOUNT, ua.CA_Status, ua.ARP_Status, ua.Trx_Number, ua.Line_Number, ua.Revenue_Amount, ua.Order_Number
FROM [USAL_ArOpen] ua
--INNER JOIN [USAL_PaymentAcks] pa
LEFT JOIN [USAL_PaymentAcks] pa ON ua.Vin = pa.VIN
LEFT JOIN [USAL_ArOpen] ON ua.Vin = pa.VIN
LEFT JOIN #kurt honunapp ON honunapp.VIN = pa.VIN
AND pa.VIN = ua.Vin
--AND honunapp.VIN = pa.VIN
AND pa.status_code = 'Y'
AND pa.service_cd = ua.Line_Type
AND ua.End_Date IS NULL
AND pa.ord_hdrnumber = ua.Order_Number
--AND Vin.[USAL_ArOpen] ua = Vin.[USAL_PaymentAcks] pa
WHERE pa.ord_hdrnumber = ua.Order_Number
--VIN.[USAL_ArOpen] ua = Vin.[USAL_PaymentAcks] pa
GROUP BY pa.vin, pa.service_cd, pa.amount, pa.ord_hdrnumber, honunapp.VIN, honunapp.RECEIPTAMOUNT, ua.CA_Status, ua.ARP_Status, UA.Trx_Number, ua.Line_Number, ua.Revenue_Amount, ua.Order_Number

Execution Plan:

Select Cost 0% Compute Scalar Cost 0% Remote Query Cost 100%

The query is also showing Null records after the known records it should execute.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-25 : 17:52:57

Still need to see the stats io output.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2014-06-25 : 18:08:05

Any chance you could create Kurt explicitly (i.e. CREATE TABLE #kurt, etc.) and make VIN the primary key? Are you using a view that has a linked server in it?

knichols
Starting Member

12 Posts

Posted - 2014-06-26 : 08:48:54

That is a really good thought.

Excuse me for asking a stupid question and I will go and search but what kind of code woould I need in SQL to create the primary key.

knichols
Starting Member

12 Posts

Posted - 2014-06-26 : 09:15:25

Here are the statistics Tara asked for:

Client Execution Time 08:08:04 17:02:25 17:00:13 16:56:26
Query Profile Statistics
Number of INSERT, DELETE and UPDATE statements 0 0 0 0 0.0000
Rows affected by INSERT, DELETE, or UPDATE statements 0 0 0 0 0.0000
Number of SELECT statements 1 1 3 1 1.5000
Rows returned by SELECT statements 0 0 64703 0 16175.7500
Number of transactions 0 0 0 0 0.0000
Network Statistics
Number of server roundtrips 3 3 3 3 3.0000
TDS packets sent from client 3 3 3 3 3.0000
TDS packets received from server 14 5 255 161 108.7500
Bytes sent from client 2066 2070 2218 2218 2143.0000
Bytes received from server 43966 5298 1029346 645467 431019.3000
Time Statistics
Client processing time 438369 37271 96333 6442 144603.8000
Total execution time 521096 119139 96748 7397 186095.0000
Wait time on server replies 82727 81868 415 955 41491.2500

knichols
Starting Member

12 Posts

Posted - 2014-06-26 : 11:11:26

SELECT *
FROM XL_USAL_GENERAL...Honda_Unapplied$
USE [FIT_TMWGeneral]
SELECT DISTINCT pa.vin, pa.service_cd, pa.amount, pa.ord_hdrnumber, honunapp.VIN, honunapp.RECEIPTAMOUNT, ua.CA_Status, ua.ARP_Status, ua.Trx_Number, ua.Line_Number, ua.Revenue_Amount, ua.Order_Number
FROM [USAL_ArOpen] ua
--INNER JOIN [USAL_PaymentAcks] pa
LEFT JOIN [USAL_PaymentAcks] pa ON ua.Vin = pa.VIN
LEFT JOIN [USAL_ArOpen] ON ua.Vin = pa.VIN
INNER JOIN #kurt honunapp ON honunapp.VIN = pa.VIN
AND pa.VIN = ua.Vin
AND honunapp.VIN = pa.VIN
AND pa.status_code = 'Y'
AND pa.service_cd = ua.Line_Type
AND pa.ord_hdrnumber = ua.Order_Number
--AND Vin.[USAL_ArOpen] ua = Vin.[USAL_PaymentAcks] pa
WHERE pa.ord_hdrnumber = ua.Order_Number
--VIN.[USAL_ArOpen] ua = Vin.[USAL_PaymentAcks] pa
GROUP BY pa.vin, pa.service_cd, pa.amount, pa.ord_hdrnumber, honunapp.VIN, honunapp.RECEIPTAMOUNT, ua.CA_Status, ua.ARP_Status, UA.Trx_Number, ua.Line_Number, ua.Revenue_Amount, ua.Order_Number

Query is running now but it is not showing the fields in the output of the honunapp alias.

What do I need to fix?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-26 : 13:30:16

What you posted is not the stats io output. I need the stats io.

SET STATISTICS IO ON

Your Query goes here.

Execute the whole thing.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-27 : 08:52:45

[code]SELECT pa.vin,
pa.service_cd,
pa.ord_hdrnumber,
Sum(pa.amount),
honunapp.VIN,
ua.CA_Status,
ua.ARP_Status,
ua.Trx_Number,
ua.Line_Number,
ua.Order_Number
FROM dbo.[USAL_ArOpen] AS ua
LEFT JOIN dbo.[USAL_PaymentAcks] AS pa ON ua.Vin = pa.VIN
AND pa.ord_hdrnumber = ua.Order_Number
AND pa.status_code = 'Y'
AND pa.service_cd = ua.Line_Type
LEFT JOIN dbo.[USAL_ArOpen] AS ua ON ua.Vin = pa.VIN
LEFT JOIN #kurt AS honunapp ON honunapp.VIN = pa.VIN
AND pa.VIN = ua.Vin
WHERE ua.End_Date IS NULL
GROUP BY pa.vin,
pa.service_cd,
pa.ord_hdrnumber,
honunapp.VIN,
ua.CA_Status,
ua.ARP_Status,
UA.Trx_Number,
ua.Line_Number,
ua.Order_Number[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources