inner join on datetime timing out
-
Hi all, I have a big table with 30 million rows (and growing) which I need to join to a table containing dates. The result is a bar graph diagram. But when I join with time over 12 hours the join times out SELECT table1.time, table1.value FROM table2 INNER JOIN table1 ON table1.time > table2.time_up AND table1.time < table2.time_down ORDER BY table1.time This should result in any thing from a couple of hundreds row to several thousands. I'm doing this in a Stored Procedure. I've put indexes on table1.time. Is this join simply to large for SQL Server 2005? Or how can I optimize this search?
-
Hi all, I have a big table with 30 million rows (and growing) which I need to join to a table containing dates. The result is a bar graph diagram. But when I join with time over 12 hours the join times out SELECT table1.time, table1.value FROM table2 INNER JOIN table1 ON table1.time > table2.time_up AND table1.time < table2.time_down ORDER BY table1.time This should result in any thing from a couple of hundreds row to several thousands. I'm doing this in a Stored Procedure. I've put indexes on table1.time. Is this join simply to large for SQL Server 2005? Or how can I optimize this search?
At a guess you have datetime fields with time extensions and need to join on the date only. You killer is the <> comparison. If this is the case I suggest you convert both dates to a string with a date only format eg
on Convert(varchar(20),table1.time,103) = Convert(varchar(20),table2.time ,103)If you are using the time as part of your join (ie between 1am and 3pm) then you have a problem and will need to work around it some other method (split the date and time fields, maybe change the time part to int for hours 0-23)Never underestimate the power of human stupidity RAH
-
Hi all, I have a big table with 30 million rows (and growing) which I need to join to a table containing dates. The result is a bar graph diagram. But when I join with time over 12 hours the join times out SELECT table1.time, table1.value FROM table2 INNER JOIN table1 ON table1.time > table2.time_up AND table1.time < table2.time_down ORDER BY table1.time This should result in any thing from a couple of hundreds row to several thousands. I'm doing this in a Stored Procedure. I've put indexes on table1.time. Is this join simply to large for SQL Server 2005? Or how can I optimize this search?
Did you use a clustered index for your "time" index on Table1? That would be considerably more efficient that using a non-clustered index.
-
At a guess you have datetime fields with time extensions and need to join on the date only. You killer is the <> comparison. If this is the case I suggest you convert both dates to a string with a date only format eg
on Convert(varchar(20),table1.time,103) = Convert(varchar(20),table2.time ,103)If you are using the time as part of your join (ie between 1am and 3pm) then you have a problem and will need to work around it some other method (split the date and time fields, maybe change the time part to int for hours 0-23)Never underestimate the power of human stupidity RAH
-
Did you use a clustered index for your "time" index on Table1? That would be considerably more efficient that using a non-clustered index.
WOW! I've tried different indexes on the large table but I never got i right. Now this baby: SELECT Intervallvarden.storhet, Intervallvarden.id_intervallvarde, Intervallvarden.varde, Intervallvarden.tid, Intervallvarden.kanal_nr, MatpunktInstrument.instrumenttyp, MatpunktInstrument.instrumentnummer FROM MatpunktInstrumentParameter INNER JOIN MatpunktInstrument ON MatpunktInstrumentParameter.id_matpunkt_instrument_parameter = MatpunktInstrument.id_matpunkt_instrument_parameter INNER JOIN Intervallvarden ON Intervallvarden.instrument_typ = MatpunktInstrument.instrumenttyp AND Intervallvarden.instrument_nr = MatpunktInstrument.instrumentnummer AND Intervallvarden.tid > MatpunktInstrumentParameter.tid_upp WHERE (MatpunktInstrumentParameter.id_matpunkt = 2575) AND (Intervallvarden.tid > '2007-10-01 00:00') AND (Intervallvarden.tid < '2007-11-30 23:00') ORDER BY intervallvarden.tid Runs in two seconds! Table "Intervallvarden" being the 30 milion rows table. Thanks allot!