April 23, 2008 at 12:58 am
Hello everyone,
My problem:
I have a database (local, SQL Standard) with 3 huge tables (Tables cannot be changed because of external logging system).
Bernt - 193 columns (FLTIME, Meting1, ..., Meting192)
Draeger - 65 columns (FLTIME, Meting1, ..., Meting64)
MDA - 49 columns (FLTIME, Meting1, ..., Meting48)
FLTIME = datetime field
MetingXX = float
The logging is triggered every second. +-600000 records a week in each table.
My program needs to export the data in a special way. In an other table is defined witch Meting (Measurement) is active and in witch Equipmentgroup it is located. I need to export every record of an equipmentgroup where on of the active measurements exceeds a loglevel. But the measurements can be spread over the three tables. For example: Bernt.Meting3, MDA.Meting5, Draeger.Meting 60 are members of equipmentgroup 5.
This is a query where I got a time out (Time out is set to 600sec).
SELECT TOP 1 B.FLTIME, B.Meting42 AS B_Meting42, B.Meting43 AS B_Meting43 FROM Bernt B WHERE ( B.Meting42 > 0 OR B.Meting43 > 0) AND B.FLTIME <= CONVERT(datetime,'2008-4-15 16:6:40',102) ORDER BY B.FLTIME ASC
And after the first found record, I add an other where part: AND B.FLTIME > CONVERT(datetime,'2008-4-15 17:6:40',102), with the datetime of the found record in order to get the next record.
Now after finding all the wanted records (726), the program send the next query to the DB. Then is when the time out is triggered. My program is build to overrule the first 5 time outs. (Also tried with 30 and 10 seconds of 'sleeping' in between.)
Does someone have an idea to make my program work?
Jo
April 23, 2008 at 2:07 am
Is it possible to send us the DDL of the table and also the execution plan which will helps more to understand.
Have you created cluster index if not then try creating cluster index on the column FLTIME if it helps then good
or else then add one non cluster indexes on Meting42 & Meting43 column.
There is no need to use aliase name B over here as you are using only one table and name of the table is also small. 🙂
"More Green More Oxygen !! Plant a tree today"
April 23, 2008 at 4:29 am
First of all, thanks for the response.
I will try adding clustered indexes on FLTIME in the three tables. And run my program again.
The execution plan can I give you, but I dont know what you mean with DDL.
And yes of course I dont need to use an alias now :).
I let you know how the indexes affect my program.
April 23, 2008 at 6:23 am
The clustered indexes on FLTIME does my program run a lot faster with almost no time outs.
I'm going to try again with non-clustered indexes on all other columns.
Thanks for the help :smooooth:
If something else comes up, I will let you know.
April 23, 2008 at 9:57 am
Don't over-index your table.
You have a lot of writes happening and every insert or update has to update the appropriate indexes. You will also get fragmentation on indexes pretty quickly without a lot of empty space in the index. The empty space may make the database huge, but the fragmented indexes can hurt performance rather than help it.
April 23, 2008 at 11:28 am
Hi,
I tried indexing all the other columns with a non-cluster index. But the max is 16. Now I only use a clusterd index on FLTIME in each table.
My program works already better now.
Thx for all the responses
greetings
Jo
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply