incremental SQL query

Question

My application has a fixed set of SQL queries. These queries are run in polling mode, ever 10 seconds.

Because of the size of the database (> 100 GB), and the design (uber normalized), I am running into performance issues.

Is it possible to get incremental changes to a given query, whenever a CRUD event happens on the DB that changes the query's result? i.e. if I am querying all of the employees with last name FOO, then I want to be notified whenever a) a new employee joins with last name FOO b) an employee with last name FOO is fired etc etc.

I am running SQL Server 2005, btw.

Thanks!!

Edit: to clarify, the database size is 100GB. Queries are not stored procedures. Database is SQL Server 2005 (but could upgrade to 2008 if need be). Because the data is very normalized, I have JOINs of 9 or 10 tables in my queries. These queries are therefore quite slow. Even with indexing.

Currently, when the poll fires, I run my query on the entire database. Whether any records have changed or not. What I would like to do, ideally, would be to register my query with the server, and get a notification whenever there are any changes to the query result set. So, if a record gets added that matches my query, just notify me of this one change. I would think this would be better performance than re-running the entire query in a poll loop. This is what I mean by incremental. That way, I could just push the changes to my client, and get rid of the polling. Thanks again for all of the comments!

Answer 1

Excuse me, but Normalised is ten to one hundred times faster than unnormalised (there are many unnormalised collections of files out there!).

Are you seriously returning a 100GB result set every 10 seconds ? That may be a silly thing to do (regardless of whether the db is normalised or not). You are hammering the server and flooding the network with 100GB of data, 99% of which does not change, every ten secs.

"Is it possible to get incremental changes to a given query, whenever a CRUD event happens on the DB that changes the [previous] query's result?"

Absolutely. ANSI SQL provides certain basics, and vendors provide extensions on top of that. Sure, being woken up by an event is better than polling, but in your case that is not the issue. If the db had some standards in it, obtaining just the rows that have changes is a no-brainer. Reasonable systems have a TIMESTAMP or DATETIME column for preventing Lost Updates and for Optimistic Locking. If you have that, and servers that can RPC to each other, writing SQL code that updates (deletes, inserts) only those rows that have changed is quite straight-forward.

If you do not have such columns implemented, then you can either put them in (no app code changes required), or write ugly code to check each column in each row (not recommended.)

If you have Sybase, it provides notifications (eliminates the Polling).

But the question begs, why do you need to copy the entire db into some other location ?

Sybase and others have a product that replicates data (eliminates your app that moves 100GB every ten secs).

Answer 2

You can use triggers to log changes in intermediate tables and then process the modification in your scheduled script. To capture changes SQL Server 2008 introduced a new feature called CDC. For more details about CDC please check this article:

http://blog.sqlauthority.com/2009/09/01/sql-server-download-script-of-change-data-capture-cdc/

Answer 3

I agree, DML triggers sound like your best option. When an insert/update/delete happens SQL fires an event. You can then use this event to update another table. But on tables that get updated often this can produce an unwelcome overhead.

http://msdn.microsoft.com/en-us/library/ms178110.aspx

Extended Events and CDC are 2008 features, so they are out.

"My application has a fixed set of SQL queries" - these are stored procs?

The query that runs every 10 seconds is optimised? SARGable parameters? A good reusable query plan.