Ad hocs queries on a heavy insert table

  • I have a table that is 16 columns wide, and has 1000 (random, unique) inserts per second.

    They run ad-hoc queries, that are taking too long to return. Here's some examples of the types ad-hoc queries they run:

    select * from logger2 where KeyId = '084e0348-bf56-460a-8067-e243689b6bc7'

    select * from logger2 where (severity=2 or severity=1)

    select * from logger2 where username = ‘joe’

    Should I look into indexing this table for 'ad-hocs'?

    Could the new 'Forced Parameterization' or sp_executesql be of use to me on this? (it's a SQL 2005 DB) I'm guessing that making the user use parameterized queries instead of the above queries would be much faster?

    Any help or ideas greatly appreciated. I'm not used to having to deal with ad-hocs.

  • The adhoc queries will cost more in terms of compile time but that is probably small compared to the read time. They may be getting autoparameterized anyway if they are very simple (and will generate consistent plans regardless of the parameter value). It's more likely that they are experiencing blocking by the inserts. Indexing may help (although obviously it will hurt insert performance) if it changes the plans to use seeks instead of scans.

    Why is there a need for adhoc queries against this table? Is there a requirement for those queries to be against live data (as opposed to say hour-old or day-old data)?

  • krypto69 (1/30/2009)


    I have a table that is 16 columns wide, and has 1000 (random, unique) inserts per second.

    They run ad-hoc queries, that are taking too long to return. Here's some examples of the types ad-hoc queries they run:

    select * from logger2 where KeyId = '084e0348-bf56-460a-8067-e243689b6bc7'

    select * from logger2 where (severity=2 or severity=1)

    select * from logger2 where username = ‘joe’

    Should I look into indexing this table for 'ad-hocs'?

    Could the new 'Forced Parameterization' or sp_executesql be of use to me on this? (it's a SQL 2005 DB) I'm guessing that making the user use parameterized queries instead of the above queries would be much faster?

    Any help or ideas greatly appreciated. I'm not used to having to deal with ad-hocs.

    If your table has no indexes at all, yes, you should at least get a clustered index on it. Use the most frequently accessed data path (not necessarily the primary key) as the clustered index (assuming it's a somewhat narrow set of data).

    As to the rest... you'll have to test it. I would absolutely suggest using sp_executesql. You're more likely to get plan reuse. Forced Parameterization might, or might not, help you. You'll need to test it.

    You could also consider taking a look at SQL Server 2008. It has the capacity to create plan stubs for ad hoc queries that reduces the memory load in the plan cache.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks for answering Matt,

    Yes this table is kind of a troubleshooting table. So, there are techs that need to see real time info.

    I was trying to think of a way I could use a force them to use a parameterized query instead of select *...

  • Thanks Grant,

    I'll experiment with both those options and look into using 08.

  • If the table has a timestamp you could index it and have them include it in all queries - presumably they only want to retrieve recent information. If they need older information, can they query a copy of the table?

  • You'd have to know before-hand what kind of stuff they query.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • It does have a timestamp, I will try that.

    THANKS MATT

    Is there some way I can force/require them to use a date (or date range)?

  • You'd have to know before-hand what kind of stuff they query.

    - GSquared

    I think I can get a pretty good list of the kinda stuff they query the most, but I'm kinda lost on what you mean..can you elaborate?

  • krypto69 (2/2/2009)


    You'd have to know before-hand what kind of stuff they query.

    - GSquared

    I think I can get a pretty good list of the kinda stuff they query the most, but I'm kinda lost on what you mean..can you elaborate?

    To put together indexed, parameterized queries, you'd need to know before-hand what kind of queries they are going to write.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • The only way you can force them to use the correct query is to create procs and deny them read access to that table - it probably won't be as easy as that 🙂 In the end it's in their own interests to shorten the query times - they might just need a push in the right direction.

  • Oh I see what you are saying..thanks Gsquared

  • Thanks Matt,

    I was kinda thinkin I may create a simple VB front end, and write a proc or two.

  • 1) nonclustered indexes are NOT helpful if the value you are searching for is not VERY specific (like <1% of the total rows in the table)

    2) at 1K/sec inserts, index maint will be a factor in performance of inserts.

    3) fragmentation will also be a significant issue

    4) do you ever prune the data? Adding 86.4M rows per day to the table will get it bloated in a hurry

    5) what about using (NOLOCK) or READ UNCOMMITTED on the SELECTs? They could be taking a long time to run simply because they are being blocked by the continuous stream of inserts. Did you check for blocking?

    6) do you need ALL data from the table (SELECT * . ..)

    7) be careful with clustered index choice. recall that a) if it isn't unique you get an additional 4-byte uniquefier for every row and b) the clustering key is carried on every non-clustered index. that first field you have looks to be a huge char type and would lead to a bloated index structure

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • While doing my testing (inserts are active) i was getting deadlocks and kicked out.

    So I added the NOLOCK, that stopped the deadlock errors..so then..

    I removed all indexes, and created a clustered on the datetime column. I then queried with and without the date. To my surprise there was no speed benefit to using the datetime clustered. I am getting a clustered index seek, I get a clustered index scan if I don't use the date. But no real increase in return times.

    Any thoughts?

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply