SQL Server: For Tune a query

Hello,

I've one table (ID (bigint), Word nvarchar(400), WordCount bigint) with records of 700000.

ID column is Primary Key with Clustered Index.

Now, if I run query SELECT * FROM tableName. It takes 11 seconds

Then, I put another non-clustered index on "Word" but still takes 10 seconds.

Any further suggestion on it?

Regards,
MohitPandit
LVL 6
MohitPanditAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
No matter how many indexes you make, if you are not using WHERE clause to filter and using *, it won't go for Index seek, it always make scan
RiteshShahCommented:
knightEknightCommented:
R.S. is correct.  If you do SELECT * FROM TABLE  the engine has no choice but to do a full table scan because you have asked it to pull everything.  The clustered index will be scanned in this case, but it won't make a difference in performance.
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

gothamiteCommented:
Do you really need to return 700,000 records in one go ? Don't know about anyone else but I would say 11 seconds isn't bad for that much data!
If you drop the nonclustered index( and primary key constraint if you can do without it) and then rebuild the clustered index on the table, you **may** get a bit of performance back as the table should be spread across less data pages.

Run it twice after doing this as the first time, SQL will load all of the data pages in to cache and the second time will read from cache, so compare the second result with your 10 second figure.
MohitPanditAuthor Commented:
I am using below query; still it is taking 6 seconds and giving 400,000 records

SELECT ID,Word,WordCount FROM IndexWordsMohit
WHERE ISNUMERIC(Word) = 1

I've non-clustered index on "Word" column only.

jmoss111Commented:
If you create an non clustered index on all included columns in the query which is called a covering index, the query will only have to go to the index and won't do a table scan
MohitPanditAuthor Commented:
Hi,

I've three column in that table and I created a non clustered index on all columns but still it is taking 6 seconds.

CREATE NONCLUSTERED INDEX [IX_TempMohit] ON [dbo].[IndexWordsMohit]
(
      [Word] ASC,
      [WordCount] ASC,
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Regards,
MohitPandit
jmoss111Commented:
400k rows?
lesouefCommented:
first 11s for 700k lines is fast, you need much more to display it.
then reducing the foundset by checking if ISNUMERIC(Word) = 1
is not using an index, so it has to be evaluated, maybe you should evaluate this into another field upon data entry (an update  for existing recs.) to be able to index this specific information and query on this new field directly.
I hope you'll never have to query on db2 via sql, you would cry for speed!
gothamiteCommented:
In oder to perform this query quickly you should add a computed column, then index that column i.e.

alter table indexwordsmohit
add is_numeric_word as CASE WHEN ISNUMERIC(Word)=1 THEN 1 ELSE 0 END

CREATE INDEX IX_is_numeric ON IndexWordsMohit (
      is_numeric_word
)

--- then to query do this

SELECT ID,Word,WordCount FROM IndexWordsMohit
WHERE IS_NUMERIC_Word = 1
MohitPanditAuthor Commented:
Hello Goatamite,

I've inserted one computed column and created non-clustered index on it. But still it is taking 6 seconds for below query.

SELECT ID,Word,WordCount FROM IndexWordsMohit
WHERE IS_NUMERIC_Word = 1

OMG

Regards,
MohitPandit
8080_DiverCommented:
Youare still struggling against the issue of how long it takes to 400,00 rows the full set of data into memory; however, because you are now using a non-clustered index, your data access is first reading the index and then reading the data.
Go to Management Studio, right click on the database, select New Query, and drop your query in that window.  Now select single left click on Query in the menu bar and select Display Estimated Execution Plan.  Now, look at the executing plan and see where the most work is being done.  Also, look at the left most icon and check the number of logical and physical reads that are occurring.
 
gothamiteCommented:
What indexes do you now have on the table? You should have just the clustered index and the one that includes the computed column. Drop any other indexes in the table and try the query again. Excessive indexes will potentially slow down your query.
8080_DiverCommented:
@gothamite,
Excessive indexes will potentially slow down your query.
How do you figure that? Excess indexes may have an impact on INSERT, UPDATE, or DELETE queries but they should have NO impact on SELECT queries.
MohitPanditAuthor Commented:
Hi gothamite,

I've done; I've created only one clustered index on comupted column but still taking 6 seconds below query
SELECT Word, WordCount
FROM IndexWordsMohit
WHERE Is_Numeric_Word = 1;

Hi 8080Diver,

Please find attached execution plan file here.

SnapShot.JPG
8080_DiverCommented:
MohitPandit,
For openers, using a calculated column for your clustered index is laible to result in problems.  A clustered index physically sorts the table based on that index's column(s).  Everytime there is an insert, update, or change to that column, the table either has to be resorted or the table gets fragmented.  
You might be better of creating a nonclustered index on that column with the other columns being INCLUDEd.  
Also, there is something known as "cardinality" that can impact the utility of an index.  If you have several thousand rows and the only column in an index is one that has either 1 or a 0, then that index is not going to be very useful . . . if you have 80,000 rows and half are 1 and the other half are 0, then using that index will mean you will scan half of the rows either way.
Is there any way that you can include any other row in your WHERE clause?
MohitPanditAuthor Commented:
Hi 8080 Diver,

I've removed clustered index and placed non-clustered index with other columns too but still taking 6 seconds below query

SELECT Word, WordCount
FROM IndexWordsMohit
WHERE Is_Numeric_Word = 1;

About your question, we need to place only either Is_Numeric_Word = 1 Or Is_Numeric_Word = 0

I am feeling that it will not come below 6 seconds :-(

Regards,
MohitPandit
8080_DiverCommented:
Given that you are doing a search on, essentially, a binary value, you may be lucky to keep it at 6 seconds. ;-)
Execute the following query and see what your results look like:
SELECT Is_Numeric_Word, COUNT(Is_Numeric_Word) AS OptionCount
FROM IndexWordsMohit
GROUP BY Is_Numeric_Word;

This will give you an idea as to the cardinality of that column.  (Brace yourself, it ain't gonna be pretty. ;-)
8080_DiverCommented:
placed non-clustered index with other columns too
Is Is_Numeric_Word the first column in the new index?  (If not, it needs to be.)
MohitPanditAuthor Commented:
Hi,

I've executed above query and it gives me count with in a second 0 -->236542 and 1 --> 420972 count.

For second thing, I've placed Is_Numeric_Word the first column and execute the query. It is taking 6 seconds.

do you have another suggestion 8080 Diver? BTW, i appreciate your suggestions which you have already given to me.

Thanks & Regards,
MohitPandit
8080_DiverCommented:
0 -->236542 and 1 --> 420972 count
What that means is that, if you only have the Is_Numeric_Word in the WHERE clause and you are looking for a 0, then the best you can hope for is an index scan on 236,542 entries and if the WHERE is for a 1, then the best you can hope for is an index scan on 420,972.  Very probably, the Optimizer is throwing up its hands and just ignoring the indexes.
If you look at the Query Plan image you provided earlier, that appears to be what it is doing . . . scanninga "range" in the index (and you had a few fewer rows at that time).
Unless you can identify the rows in a different manner, then this is probably about the best you can do.  Sorry to be the bearer of bad news. :-/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MohitPanditAuthor Commented:
Thanks for your valuable suggestion.

Now, conclusion, I can't place more fields in WHERE clause and I can't achieve to reduce the time; really bad.

Anyways thanks again for your time.
Take care
8080_DiverCommented:
Sometimes the simplest sounding tasks can prove to be the most difficult to achieve.  Sorry. :-/
lesouefCommented:
i think diver is right: if you want to convince yourself, try selecting *, you should get the same roughly, as your condition filters half of the records (assuming as many "0" et "1" in the table) only.
I have here a table with 244klines and it's not any quicker at all, 6s looks very reasonable unless you db runs from memory instead of a disk, and has been loaded already by a previous query.
MohitPanditAuthor Commented:
Ok, Thanks,

I am closing this question.

Thank you very much again.

Regards,
MohitPandit.
8080_DiverCommented:
After considereable assistance, Mohit is now asking for the question to be deleted and no points awarded?  This is definitely not in the spirit of this site's appraoch to requesting assistance and assistance being provided.  
While it is unfortunate that Mohit cannot get his data retrieved any faster, that is due to the nature of the data and the way he is selecting it and not due to the inability of any who assisted him in realizing the reason behind the failure.  I believe that Mohit should either award points based on those who provided insight so that he could understand why he couldn't get the data faster or that, if the question is otherwise closed, the points whould be forfeited.
I do not believe this question should be deleted because it could be instructive to others.
Anthony PerkinsCommented:
I recommend that the points are split between the solution to the original question here http:#a31743362 and the morphed version here http:#a32391034, http:#a32697905 and http:#a32704846
MohitPanditAuthor Commented:
That point helped
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.