x

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.

more ▼

asked Aug 31, 2017 at 09:46 PM in Default

avatar image

artistlover
866 49 64 68

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Sep 01, 2017 at 09:55 AM

avatar image

anthony.green
3.1k 1 4 6

Or if that's a normal expected query, you could also right size the drive containing TempDB by expanding the drive.

Sep 01, 2017 at 01:20 PM JohnM

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.

Sep 01, 2017 at 01:41 PM artistlover
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1096
x79
x27

asked: Aug 31, 2017 at 09:46 PM

Seen: 61 times

Last Updated: Sep 01, 2017 at 01:41 PM

Copyright 2018 Redgate Software. Privacy Policy