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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 70

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?
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.


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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change this SQL to get all nodes 3 54
SSIS - how to skip a step if it is spinning indefinitely in a step? 14 41
ms sql + help with query 2 43
Creating a View from a CTE 15 40
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

738 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