I have two tables that I need to join but when I try to run the SQL script it is timing out.
Table one is called ObjectRating Table two is Product
Table one contains a column called ObjectID table two as a column called ID which is the same as Object ID. So that is the joining value.
Table two also contains column Name that I want.
So how can I write a SQL script to get this information? My thoughts were
SELECT
top 400000
kor.ObjectId,
kor.CreatedAt,
kor.customerID,
p.Identifier, p.Name,
p.NameShort,
p.EAN,
otg.GroupID, m.Name as 'BRAND'
FROM StarProduct p
INNER JOIN StarObjectToGroup otg on otg.ObjectID = p.ID
INNER JOIN KicksObjectRating kor on kor.ObjectID = p.ID
INNER JOIN StarManufacturer m on m.ID = p.ManufacturerID
LEFT JOIN StarGroup g on g.ID = otg.GroupID
and kor.CreatedAt between '2016-02-19' and '2016-03-11'
order by kor.CreatedAt
Usually you see timeouts when there are a large number of rows returned. That can happen when your query needs to return a large number of rows, or when you don't have proper indexes on the tables. When you don't have proper indexes, SQL Server may have to scan the entire table to get your results.
Try changing TOP 400000 to TOP 1 and see if the query runs.
See how many rows there are in each of these tables.
See if you have indexes on the columns used in the join. For example, StarProduct column ID, StarObjectToGroup column ObjectId etc.
Enable show query plan (control-m) and run the query (with the top 1) which will show you what operations are consuming the most resources.
Shouldn't be any timeout limit if you run the script from SSMS (rather than within, say, an APP)
That would enable you to find out how long it actually takes, and then you would have a baseline to compare any improvements (e.g. adding INDEXes) against
Most likely the best way to address your performance issues with the
KicksObjectRating kor
table is to change its clustering key from id/objectid to (CreatedAt, ObjectId).
You can leave a separate, nonclustered index on id/objectid.