Slow Query - Date in text format

[hellferret]

OK. I have a third party database that I need to query. There is 1 million records in the table, each with a date field in text format (I can't change this). I want to return all fields after a certain date but this is painfully horribly nastily slow and keeps beating me with the time-out stick.

Can anyone throw me a fricken bone here?
HF

 

[r937]

not sure how practical this is...

you might not be able to change the 3rd party database, but you can build a lookup table on it

create another table, populate it with the pk and a datetime field, and find a way so that updates, inserts, and deletes to the 3rd party database are also repeated in the date lookup table

select * from 3rdpartytable
where pk in
( select pk from lookuptable
where datetimefield > somedate )

rudy

 

[MeanGreen]

When you say "text" field, are you referring to the text datatype or varchar? Sounds like you need to create an index to improve retriving your data. You might also create a personal view with the column converted to datetime datatype. To do this just create a select statement using CAST or CONVERT on the "text" field to datetime. Suppose you have a column called action_date that is in varchar format in a table called table1. To create a view you would do the following:

create view v_table1 as
select convert(datetime,action_date) as Action_Date from table1

Then all you have to do is:

select * from v_table1
where Action_Date between '1/1/2003' and '1/5/2003'

Hope this helps.

 

[r937]

yeah, but meangreen, the view is gonna run the same (slow) way that his query did

rudy

 

[tlbroadbent]

How many rows does the query return? Does the query perform a group by, order by or join operation. Even if you index and properly query the table, the number of rows returned may still cause timeout in the client application. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[SQLSister]

I like r97's suggestion. Put triggers on the original table to keep your lookup table up-to-date and then use it for the date calculations. Also, don't let your third party vendor get away with this sort of poor design. Complain to them and complain loudly about their poor design choices, maybe they will fix it in the next version. I'd also consider looking for a different third party app. If this one is so poorly written that they didn't even use a basic like defining a date field as a date data type, then the rest of the application is probably poorly written as well and may in fact be costing your company money though its slow processing and bugs.