SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query is running very, very slowly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

knichols
Starting Member

USA
12 Posts

Posted - 06/25/2014 :  16:50:33  Show Profile  Reply with Quote
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

USA
36617 Posts

Posted - 06/25/2014 :  17:05:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 06/25/2014 :  17:12:09  Show Profile  Reply with Quote
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.
Go to Top of Page

knichols
Starting Member

USA
12 Posts

Posted - 06/25/2014 :  17:46:35  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36617 Posts

Posted - 06/25/2014 :  17:52:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
Still need to see the stats io output.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 06/25/2014 :  18:08:05  Show Profile  Reply with Quote
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?
Go to Top of Page

knichols
Starting Member

USA
12 Posts

Posted - 06/26/2014 :  08:48:54  Show Profile  Reply with Quote
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.
Go to Top of Page

knichols
Starting Member

USA
12 Posts

Posted - 06/26/2014 :  09:15:25  Show Profile  Reply with Quote
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
Go to Top of Page

knichols
Starting Member

USA
12 Posts

Posted - 06/26/2014 :  11:11:26  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36617 Posts

Posted - 06/26/2014 :  13:30:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 06/27/2014 :  08:52:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000