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.
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.
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.
18 People are following this question.
