Improving queries on datetime column

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

General SQL Server Forums

Database Design and Application Architecture

Improving queries on datetime column

Author

Topic

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2008-07-28 : 18:18:10

I often need to query certain tables that contain datetime columns. These datetime columns are not included in any index. The tables are used by our third-party ERP sytem and I'm not sure it would be a good idea to add my own index. I'm wondering what would be the best approach for improving query performance.

For example, one table is basically a list of job operations (There are many more columns than the ones I'm listing, these are just for example. Underscores are just to make it readable.) Job and operation make up the key.

Job__Operation__PlannedStart__PlannedEnd__ActualStart__ActualEnd
100__1__________7/1/08________7/2/08______7/2/08_______7/2/08
100__2__________7/2/08________7/3/08______7/2/08_______NULL
101__1__________7/1/08________7/1/08______7/1/08_______7/1/08
101__2__________7/1/08________7/2/08______7/1/08_______7/2/08
101__3__________7/2/08________7/2/08______7/2/08_______NULL

Different queries are need for reports of -
*operations planned to start on a particular date
*operations planned to end on a particular date
*operations actually started on a particular date
*operations actually finished on a particular date

The only option I can think of is to make my own tables that are indexed by date and that are updated periodically by a scheduled job. Then join those to the main tables in my queries. For example -

OperationsPlannedToStart
Date___Job__Operation
7/1/08_100__1
7/1/08_101__1
7/1/08_101__2

OperationsActualComplete
Date___Job__Operation
7/1/08_101__1
7/2/08_100__1
7/2/08_101__2

...and so on.

Does anyone else have a suggestion?

Thanks,

Kevin

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-29 : 04:57:04

Sugestions...

1) Find out if you can index those columns / what impact on other queries that would have. I think this is the best approach.

2) data duplication is generally a bad idea. Dealing with synchronisation is a pain in the ass

3) [This is probably inapplicable if your database is hosted by a third party] Is there legacy data that you seldom need to search in these tables. Is it possible to set a cut-off point (records over 5 years old or whatever) and hive old records off to another partition / table

-------------
Charlie

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2008-07-29 : 15:01:15

Thanks for the advice. I'll look into adding indexes and archiving.

Kevin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources