• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

SQL Recordcount

I keep having timeout issues on one of my Visual Basic programs that is talking to an SQL db.  I ran into a tool a long time ago that can help your queries to be more efficient.  I think it analyzed the incoming queries and built indexes to make them more efficient.  It was in SQL, not a 3rd party tool.

Does that ring a bell with anyone?
1 Solution
Chris LuttrellSenior Database ArchitectCommented:
Are you talking about running Profiler and then running it through the Query Analyzer to recommend indexes?
Éric MoreauSenior .Net ConsultantCommented:
Database Engine Tuning Advisor - it is installed with SQL Server
David ToddSenior DBACommented:

Run your queries from SSMS (SQL Server Management Studio)

Select the option for execution plan - actual or estimated - either will do.

When looking at the plan, note the green line at the top that says something to the effect of missing index.

Look at the cost of the queries.

That answers your general question, now for some specifics questions for you
- is the entire thing now running really slow, or just particular queries?

- Have you got any indexes at all?

- When was the last time you did any maintenance on those indexes, an index defrag if you will? (Note that there are technical differences between an index rebuild and an index defrag, and differences between how to do this in different versions of SQL ... )

A default starting point is
Every table to have an id that is an int identity
Every table to have a clustered primary key on that id
Foreign key constraints to be applied
Every column that has a foreign key requires an index on that column.

Now of course there are all kinds of excuses for not doing things like that, and I tend to break them myself, but do some of the above and you could be surprised at the performance improvement.

Last suggestion -
From SQL's point of view, start with the most expensive queries. In SSMS, connect Object Explorer, Right click on the server, select reports, select standard reports, look at the dashboard, and anything with queries. You are looking for the top queries or most expensive queries. Starting there might get you most benefit for effort.


PS Yes the index tuning adviser is there, but it can be an awfully big hammer to swing and what is sometimes a very small nut.

Can you tell us more about your performance issue - is it the server being old and tired and therefore slow, or your code?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

hrolsonsAuthor Commented:
Wow, amazing answer.  I think I see one of the problems.  In my code:

    sqlStr = "select * from myTable"
    Rst.CursorLocation = adUseClient
    Rst.Open sqlStr, MyConnObj, adOpenKeyset, adModeReadWrite
    Record_Count = Rst.RecordCount

It jumps out that I shouldn't be using adModeReadWrite.  I will research a more appropriate connection.
Anthony PerkinsCommented:
For one you should not be returning a result set to only turn around and get the count of rows.  That is (to put it bluntly) nuts.  You should be doing something like this:

sqlStr = "SELECT COUNT(*) RecordCount FROM myTable"
Rst.Open sqlStr, MyConnObj
Record_Count = Rst.Fields("RecordCount").Value

You can even make it a lot faster if you have a CLUSTERED INDEX on your Table, but try that for now.
David ToddSenior DBACommented:
To comment further, the paging problem - returning an entire recordset for one page on screen -  is eased somewhat if not completely solved by some of the windowing functions in SQL 2012 ...

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now