?
Solved

SQL Recordcount

Posted on 2013-01-25
6
Medium Priority
?
316 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
[X]
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
6 Comments
 
LVL 27

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
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…
Suggested Courses

762 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