0

I'm currently designing a database application that executes SQL statements on a SQL Server linked to the PCs via ODBC drivers (SQL Native Client v10, Local Network, Network Latency >1ms, executed from withing a MS Access 2003 Environment).

I'm dealing with a peculiar select query that is executed often and has to iterate through an indexed table with about 1.5 million entries. Currently the query structure is this:

SELECT *
FROM table1
WHERE field1 = value1
  AND field2 = value2
  AND textfield1 LIKE '* value3 *'
  AND (field3 = value3 OR field4 = value4 OR field5 = value5)
ORDER BY indexedField1 DESC

(Simplified for reading comprehension and understandability, the real query can have up to 4 bracketed AND connected OR blocks, and up to a total of 31 AND connected statements).

Currently this query takes about ~2s every time it gets executed. It returns somewhere between 1.000 and 15.000 records in usual production. I'm looking for a way to make it execute faster or to restructure it in a way to make it work faster.

Coworkers of mine have hinted at the fact that using LIKE operators might be performance inefficient and that restructuring the OR statements in brackets could bring additional performance.

Edit: Additional relevant information: the table that is being pulled from is VERY active, there is an entry roughly every 1-5 minutes into it.

So the final question is:

  • Given my parameters outlined above, is this version of the query the most simplistic I can get it.

  • Can I do something to otherwise speed up the query or the execution time thereof.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Magisch
  • 7,312
  • 9
  • 36
  • 52
  • @jarlh considering it gets re-queried less then every minute usually, yes. – Magisch Nov 04 '15 at 14:56
  • Sorry for deleting my comment, it was "is two seconds for 1000 - 15000 rows that bad?". – jarlh Nov 04 '15 at 14:56
  • @jarlh Its not ideal currently since the query has to run more often then every minute. – Magisch Nov 04 '15 at 14:58
  • which columns are indexed ? – ogres Nov 04 '15 at 14:59
  • @ogres The primary key ("entry") and one of the data fields (always included in the select query) – Magisch Nov 04 '15 at 15:00
  • Do you absolutely need to search on `'* value3 *'`? Is there anything you can do to pre-calculate that, for example adding a new column and keeping it up to date when you INSERT or UPDATE based on whether `value3` was put in the text field? That'll likely be what's killing you, as you can't possibly index that -- it'll have to scan through every row, looking for `value3` in that text field, every time it runs. The other fields you can index, but you can't use an index if you're using a wildcard at the *front* of a search (as the order of the data isn't relevant to what you're looking for.) – Matt Gibson Nov 04 '15 at 15:02
  • Try starting with creating 2 index for field1 and field2 , then, instead "LIKE" try Full-text-search , https://msdn.microsoft.com/en-us/library/ms142571.aspx#like – ogres Nov 04 '15 at 15:03
  • @MattGibson Its an arbitrary form filter in a text field. Unless you have some way to replicate that exact functionality somehow I can't really skip that :(. Value3 is generated by text entry at runtime. – Magisch Nov 04 '15 at 15:03
  • @Magisch Then I think the only way you can possibly speed it up is to implement [SQL Server's full text search indexing](https://msdn.microsoft.com/en-us/library/ms142571.aspx) and use that, but that's quite a big chunk of work and I don't know how it interacts with Access. It was absolutely designed to solve exactly your problem, though. – Matt Gibson Nov 04 '15 at 15:04
  • @MattGibson Are you saying that using only 1 wildcard at the back would allow me to index the text fields and speed up the query through it? Because I could probably do that by splitting some columns systematically and tacking them together again. – Magisch Nov 04 '15 at 15:07
  • Yes; a query for `LIKE '%something'` can't use an index. A query for `LIKE 'something%'` (I'm using SQL Server native wildcards here, '%' is the equivalent to Access's `*`) *can* use an index. An index is basically just your data sorted into order; imagine looking up in a phonebook all the people whose names begin with 'L' -- easy. Now imagine looking up in a phonebook the people whose names *end* with 'L'... – Matt Gibson Nov 04 '15 at 15:10
  • @MattGibson Can you formulate that into an Answer? Meanwhile i'll try and test wether that actually speeds up my query and ill also look into that full text thing and see if I can implement that for my dev copy of the DB. – Magisch Nov 04 '15 at 15:12
  • (Note that this all depends on the cardinality of your data, too. If the data is narrowed down to a few hundred rows by your *other*, non-text predicates, then you might just need to put an index on those to improve things. You should share your indexes, typical data distribution, and probably a query plan from an example slow query.) – Matt Gibson Nov 04 '15 at 15:13
  • @MattGibson Alright, ill try indexing the other, more narrowing fields first. Is there a limit of how many of those I should index before it stops making sense? In the least complex scenario my query runs through OR blocks from at least 12 integer fields. – Magisch Nov 04 '15 at 15:15

1 Answers1

1

General query optimisation is beyond the scope of a single answer, though there may be some help to be had on http://dba.stackexchange.com. However, you should learn how to read a query plan and figure our your bottlenecks before you start optimising.

(The way I'd do that would be to take a few typical queries and look at their estimated execution plan through a tool like SQL Server Management Studio. You may have to try to dig out the real SQL Server query that's resulted from your Access query, which your DBA might be able to help with. I'm assuming that your Access query is actually being translated into a SQL Server query and run natively on the server; if it's not then that will be your big problem!)

I'm going to assume you've indexed every column used in every predicate in your WHERE clause and still have a problem. If that's the case, the suspect is likely to be:

AND textfield1 LIKE '* value3 *'

Because that can't use an index. (It's not SARGable, because it has a wildcard at the beginning, so an index won't be any help.)

If you can't rearrange your search or pre-calculate this particular predicate, then you basically have the problem that Full-Text Searching was designed to solve by tokenising and pre-indexing the words in your text, and that will probably be the best solution.

Community
  • 1
  • 1
Matt Gibson
  • 37,886
  • 9
  • 99
  • 128
  • The part you just edited in. How do I find out wether or not my Access query is translated properly? Im using a simple LAN ODBC Connection with SQL Native Client 10.0 as driver and the querys get executed via standard data source method in VBA Access 2003. – Magisch Nov 04 '15 at 15:23
  • Good question! I'm afraid I can't remember enough about Access 2003 to know. It's been more than a decade since I used it! You may want to tag this question for Access, too, as if you're doing the work on the client rather than the server that will likely have an enormous impact on performance. (From memory, can you try formulating your query in proper SQL Server rather than Access syntax and try it as a "pass through" query in Access? In your example, all you'd need to do to make a valid SQL Server query would be converting your `*`s to `%`s in the LIKE predicate.) – Matt Gibson Nov 04 '15 at 15:28
  • Also, try just running your query directly on the server using SQL Server management studio and see how fast it goes, but do bear in mind that SQL Server will cache results, so execute a `DBCC DROPCLEANBUFFERS` (*not* on your production server!) before each test. – Matt Gibson Nov 04 '15 at 15:32
  • 3
    turns out indexing the rest of the fields in the search and dropping the leading wildcard by splitting the text field made the entire query process up to 80% faster in heavy cases (one example case I picked out went from taking a minute to just 3s!) Thank you for your effort in helping me resolve this problem. – Magisch Nov 06 '15 at 13:49
  • 2
    Due to how much this answer has helped me both in learning SQL better and because my program now actually runs at decent speed I feel like this is worthy of an additional bounty. – Magisch Apr 11 '16 at 06:38
  • Thank you; that's very kind. – Matt Gibson Apr 11 '16 at 08:54