SQL Recordcount

Posted on 2013-01-25
Last Modified: 2013-02-01
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?
Question by:hrolsons
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38821635
Are you talking about running Profiler and then running it through the Query Analyzer to recommend indexes?
LVL 69

Expert Comment

by:Éric Moreau
ID: 38822304
Database Engine Tuning Advisor - it is installed with SQL Server
LVL 35

Expert Comment

by:David Todd
ID: 38823443

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?
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.


Author Comment

ID: 38827378
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.
LVL 75

Accepted Solution

Anthony Perkins earned 500 total points
ID: 38829355
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.
LVL 35

Expert Comment

by:David Todd
ID: 38837891
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Complex SQL script 1 31
sql query Help 12 52
SQL Exceptions 3 36
Trying to identify overlapping date ranges 5 12
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now