Optimising Joins With Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Optimising Joins With Dates

Author

Topic

scullee
Posting Yak Master

103 Posts

Posted - 2003-03-09 : 22:59:01

I am currently writing an import stored procedure for a system that has an aweful structure.

I have most of the system going but i am doing a little optimising.

To do the import i have loaded all of the data into a temporary table and are processing it with a series of SQL queries. One of these queries requires me to join a field that contains a date time to another field that contains just the date part of that field.

Whats the best way to perfom this without having to do a convert on the fields?

scullee
Posting Yak Master

103 Posts

Posted - 2003-03-09 : 23:02:48

Oops, i forgot.

Just to complicate the task, the import file contains the dates in a weird format so i will probably have to convert that from text to a date first too.

My current sql query that works is

 
	SELECT FDFImportParsed.id, sale.ID, FDFImportParsed.Field2 as counter , FDFImportParsed.Code, TillDefault.TillID, 
      FDFImportParsed.Field3 as ChequeDate, Sale.ID as SaleID, FDFImportParsed.Field3
	  FROM FDFImportParsed 	
      LEFT OUTER JOIN TillDefault ON FDFImportParsed.Code = TillDefault.Code 
      LEFT OUTER JOIN Sale ON FDFImportParsed.Field2 = Sale.Counter AND TillDefault.TillID = Sale.TillID AND
       convert(varchar,convert(datetime,FDFImportParsed.Field3,1),103) = CONVERT(varchar, Sale.[Date], 103)
	  WHERE (FDFImportParsed.Field1 = '32')

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-10 : 00:19:02

Sale.[Date] just contains the date so if you don't convert it then any indexes on it can be used.

I would prefer 112 rather than 103 as a date style as it is unambiguous but it won't affect performance.

you can use dataediff to check the date but I don't think it will help.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

scullee
Posting Yak Master

103 Posts

Posted - 2003-03-10 : 00:49:48

Why would 112 give any better performance than 103?

They are both just text dates

112 = yyyymmdd
103 = dd/mm/yyyy

The problem is that the indexed field is the one with the date time and i want to ignore the time (remove it) so it wont use the indexes.

Edited by - scullee on 03/10/2003 00:51:11

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-10 : 02:19:15

>> I would prefer 112 rather than 103 as a date style as it is unambiguous but it won't affect performance.

i.e. it won't give better performance.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

scullee
Posting Yak Master

103 Posts

Posted - 2003-03-10 : 04:45:49

Oh im an idiot.

My eyes were reading 1 thing but the brain was reading another

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources