Geek Answers Handbook

Sql server indexing in a table without joins and predictable query

Well, before I start, I want to say that I'm completely new to this Sql indexing.

I have a table that does not join to anything. It has the following columns:

Id (int) String1 (nvarchar(10) String2 (nvarchar(50) DateTime1 (date) DateTime2 (date) DateTime3 (date)

I have about 100 million rows in this table. And do a search on it very slowly, so I believe that I need to add some indexes.

I will only run the following queries:

Request 1

 select * from Table where String1 = "Blah" and String 2 = "Blah" and DateTime1 <= {someTime1} and DateTime2 >= {someTime1}

Request 2

 select * from Table where String1 = "Blah" and String 2 = "Blah" and DateTime2 >= {someTime1}

Request 3

 select * from Table where String1 = "Blah" and String 2 = "Blah" and DateTime3 >= {someTime1}

Note that they represent almost the same query, except that they have a slightly different date comparison. In addition, sorting is not a problem.

So I tried adding a nonclustered index to the columns String1, String2, DateTime1, DateTime2. Fulfilling query 1 here, what I see:

  • Now it is much faster, but loading takes about 20 seconds.
  • I notice that for the same query (with the same search parameters), if I call it again, it will return data in less than a second.
  • I notice that if I run query 1 with some other parameters, it will again take 20 seconds to load.
  • I noticed that my RAM rises and stays after the request.

So here are my questions:

  • Am I doing it right? Why boot for 20 seconds? Isn't it really fast after adding an index?
  • What does the Sql server do with my RAM? Do I need more RAM because I have a large table?
  • Do I need to add new indexes for query 2 and query 3? Or is the index that I added already good enough for the other 2 queries?

Thanks,

Chi

+4
source share
5 answers

  • Analysis and compilation of the request and, possibly, reading from disk. That is why it works fast the second time. And it takes time to compile again when changing the parameters.

  • Data cache. aka Buffer pool. No more RAM will go wrong with SQL Server, as usual.

  • Queries 1 and 2 match, query 3 is different.

I would suggest 2 indexes to start with

  • String1, String 2, DateTime2, DateTime1 INCLUDE DateTime3
  • String1, String 2, DateTime3 INCLUDE DateTime2, DateTime1

Other thoughts relate to data types ... small is better if the course

Edit:

Reading disks will happen in memory (simply), so more RAM will help. However, I suspect that 20 secodns is compilation + statistics, etc., and not reading from disk

+2
source

I would recommend getting to know SQL Server Database Tuning Advisor (DTA) and Profiler.

Here is a good article:

http://www.zimbio.com/SQL/articles/655/How+Tune+Database+Using+Database+Tuning+Advisor

DTA does not always give you great recommendations, but usually this is a good start. If you use a profiler, you can monitor your database during the day, and then transfer it to the DTA.

There are also other things to consider, for example:

  • How often specific mileage queries will be asked
  • How often records tables are inserted or updated

Keep in mind that indexes will slow down your insertions and updates.

Another important thing is to make sure that you start with the same baseline for every request that you run. I suspect that you see results 1 and 2 in your question because you have data in the cache. Here is a good link that talks about how test sql scripts.

0
source

The problem with query 1 is that it has two independent range conditions. More recently, there was a very similar question. I explained this specific problem there: How the database stores data inside the B-Tree / B + tree

regarding request 2:

if you use the following index:

 String1, String2, Date2, Date 1

it can serve query2 well and does not change query1 (except that the condition date1 is much more selective than date2).

query3 may require an additional index:

 String1, String2, Date3

However, I do not like two indexes with the same prefix. I would probably include String1 and String2 - just in case the request only has String2.

Want to better understand all of this indexing stuff? Take a look at my free eBook "Use the Index, Luke"

0
source
  • Am I doing it right? Why boot for 20 seconds? Isn't it really fast after adding an index?

-> I think you are doing everything perfectly, but I also see the selectivity of the data in your request. How many records will be returned in your typical 100,000,000 request? If your result set is small (3 ~ 5% of the whole table), indexing is a good resolution.

  • What does the Sql server do with my RAM? Do I need more RAM because I have a large table? -> The DBMS moves the data (in blocks) from the physical storage to the RAM to execute the request. Also your request needs to be parsed and it will consume some memory.
0
source

More RAM will help depending on your operating system on which SQL Server is installed, the version of SQL Server and how much RAM you have. All of them do not have the same restrictions on RAM.

For future use, use Query Analysis in the Database Configuration Advisor in SQL Server Management Studio (SSMS). You may not need it now, but it can help when tables become more complex.

Do you have a primary key in this table? Just curious, you never talked about this.

0
source

More articles:


All Articles