question

artistlover avatar image
artistlover asked

tsql help

I have to pull tons data from multiple tables. select [a].[dbo].[SC_Shipment_2017].itemcode ,[a].[dbo].[SC_Shipment_2017].[Customer Number] ,[a].[dbo].[SC_Shipment_2017].[QuantityShipped] ,tblvendor.Address_1 ,tblvendor.City , tblvendor.state ,tblvendor.zip FROM [a].[dbo].[SC_Shipment_2017] inner join tblvendor on [dbo].[SC_Shipment_2017].vendorno = tblvendor.Vendor_Key inner join [dbo].[tblZipExpanded] on tblvendor.[state] = dbo.tblzipexpanded.[state] order by tblZipExpanded.zipcode obviously I have something wrong. It has been running for 15 minutes Now with above code that must be really worst code.. > Msg 1105, Level 17, State 2, Line 1 > Could not allocate space for object 'dbo.SORT temporary run storage: 141052529082368' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. So tempdb is filling up on drive.
t-sqltempdbspace
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
anthony.green avatar image
anthony.green answered
If that's how to join the tables together you don't have anything wrong. As for the slow query, ideally we need to actual execution plan to diagnose what is going on, but first places to start would be ensuring indexes, statistics are all created to support the query and up to date. For the TempDB issue, the ORDER BY is killing it as all ORDER BY's are done in TempDB, so if you have a 8GB TempDB then your data result set sort is larger than 8. Try reducing the amount of data your bringing back with a where clause.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Or if that's a normal expected query, you could also right size the drive containing TempDB by expanding the drive.
1 Like 1 ·
artistlover avatar image artistlover commented ·
wow so that is so cool. my query is good. Yes I am having the drive increased. Little more detail I can give you. my tables are customer master, vendor master and zip master. company a sends shipment information but I need to pull vendor address into table with the item shipped. eventually I will need customer information also. FYI, I didn't know I was going to be writing all this code. Job grew and I want to learn better how to code and be more efficient because I love the job. Thank you for help.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.