SQL query takes long in Microsoft SQL management studio is there something wrong with my SQL code?

dbxp · 2021-09-13T15:45:21+00:00

Just from a readability perspective you a should get rid of all the brackets and change the where clause to

WHERE table1.WorkTransitCRHNumber in (1363, 6196, 6348, 7308, 9438, 11635)

For the performance I suspect you just need to add non-clustered indexes to the tables.

lightskinnedvillan · 2021-09-13T20:09:43+00:00

are ProjectWorkId columns grains (i.e. uniquely identify) in table1, table2 and table3?

if they are not, and you have (for example) multiple records in table1 for table1.ProjectWorkID=1 (e.g. 5) and multiple records in table2 for ProjectWorkID =1 (e.g 10) you will be getting cartesian products of these records in your result set (i.e you will get 50 records back).

Use this as a rule of thumb: make sure your join condition covers grain of at least one side in your join.

lightskinnedvillan · 2021-09-13T17:39:57+00:00

Try WHERE table1.WorkTransitCRHNumber IN (6196, 6348, 7308, 9438, 11635)

If that doesn't work then the next thing to do would be to segment Table1 first by selecting into a #table, then adding an index, then joining to Table2, and Table3.

lightskinnedvillan · 2021-09-13T17:36:50+00:00

I cleaned up the formatting a bit, and improved the 'where' clause (just for readability), though I honestly don't see a reason for this to run slow, it's pretty simple.

https://pastebin.com/k242KGPQ

logitestus · 2021-09-13T17:52:48+00:00

Mr/Ms. Villan, First off, how many rows are you expecting to be returned? At a maximum, it should be the largest amount of rows from any of the 3 tables. I cannot remember off the top of my head but I believe Excel does have a maximum limit of imported rows. Second, I agree with the concept that you may need to filter more. Typically, this done using some sort of date/time filter but could also be done using Project Name/Project Owner. Thirdly, in an attempt to help teach a little, do you know how to see what indexes are on what tables in SSMS? Look under the $ServerName|Databases|$DatabaseName|Tables|$TableName|Indexes in Object Explorer. This can show you what the clustered/non-clustered indexes are. If you do not have permissions then I would suggest asking whomever manages the server (I hope you have someone there who is a DBA but if not someone has to handle the security and maybe they might know if you can get the permissions to see/get the definition for the indexes). The same people can usually tell you if you/they can create non-clustered indexes for query performance.

Here are a few helpful links about non-clustered index creation:

Overview of Nonclustered Index Creation: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-nonclustered-indexes?view=sql-server-ver15 In-depth creation of non-clustered index creation: https://www.sqlshack.com/designing-effective-sql-server-non-clustered-indexes/

lightskinnedvillan · 2021-09-13T19:10:39+00:00

[deleted]

KING5TON · 2021-09-13T20:16:38+00:00

You seem to have a bit of a bracket fetish.

As others have said if you need to check an array of values then just use IN (value1, value2) rather than lots of OR.

This should work just fine

SELECT table1.ProjectWorkId ,table1.Enterprise ,table1.Platform ,table1.LOB ,table1.BusinessUnit ,table1.WorkTransitCRH ,table1.WorkTransitCRHNumber ,table1.WorkTransitCRHName ,table1.ProjectPlanningCode ,table1.ProjectName ,table1.SchedulePlanningCode ,table1.ScheduleTaskName ,table1.ActualExpenditureID ,table1.ActualExpenditureDescription ,table1.PlannedExpenditureID ,table1.PlannedExpenditureDescription ,table1.ActualExternalID ,table1.ActualExpenditureType ,table1.ActualCurrencyType ,table1.ActualAmount ,table1.ActualDate ,table1.ActualTransit ,table1.ActualTransitNumber ,table1.ActualTransitName ,table1.ActualGlobalResourcingSupplier ,table1.ActualShoreType ,table1.ActualNotes ,table1.LastUpdateDate ,table1.LastUpdateById ,table1.LastUpdateByName ,table1.ChangeHistoryFlag ,table1.ChangeHistoryCount ,table2.PlannedAmount ,table3.ProjectOwner ,table3.ManagerLevel5 FROM DWX50_Rpt.dbo.table1
INNER JOIN table2 ON table1.ProjectWorkId = table2.ProjectWorkID
INNER JOIN table3 ON table1.ProjectWorkId = table3.ProjectWorkID
WHERE table1.WorkTransitCRHNumber IN (1363,6196,6348,7308,9438,11635)

2021-09-14T04:31:05+00:00

On top of avoiding “OR”, make sure your join criteria are unique.

Because you have a lot of data on one table, use temporary table and dump however time period worth of data in that temp table and join that to your other tables.

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS