Solved

SQL Recordcount

Posted on 2013-01-25
6
303 Views
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?
0
Comment
Question by:hrolsons
6 Comments
 
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?
0
 
LVL 70

Expert Comment

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

Expert Comment

by:David Todd
ID: 38823443
Hi,

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.

HTH
  David

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?
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:hrolsons
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
    Rst.Close

It jumps out that I shouldn't be using adModeReadWrite.  I will research a more appropriate connection.
0
 
LVL 75

Accepted Solution

by:
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
Rst.Close

You can even make it a lot faster if you have a CLUSTERED INDEX on your Table, but try that for now.
0
 
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 ...
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

770 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