Fastest SQLServer 2000 query

Posted on 2004-11-08
Last Modified: 2010-04-23

What would be the quickest way to query a database ONLY to check if any rows were returned by the query?
So that in pseudo code:

If NoRows = True Then
'Do something
'Skip and DON'T do anything
End If

I can pass a complex query in as a string or call a stored procedure. I'm not sure how to do that. Any suggestions?


Chris Chang
Question by:Trancedified
    LVL 4

    Accepted Solution

    What language are you using ? I'll VB.NET here,

    'pls indentify  MySQLCommand here
    Dim MySQLCommand As SqlCommand = New SqlCommand(your query, MySqlConnection)

    'but you should use Stored Procedure, let the database take the security and performace for you. acctually use SP will do   great performance.
    'with SP with no parameter
    Dim MySQLCommand As SqlCommand = New SqlCommand(sp_name, MySqlConnection)
     MySQLCommand.CommandType = CommandType.StoredProcedure

    'with SP with parameters
    Dim MySQLCommand As SqlCommand = New SqlCommand(sp_name, MySqlConnection)
    MySQLCommand.CommandType = CommandType.StoredProcedure
    Dim parameter As SqlParameter
    'pls add create and add the parameters here.
    'and now add it too SP
     For Each parameter In parameters

    '.... pls use your SQLCommand
    dim result as SQLDataReader
    dim Details as String
    result = MySQLCommand.ExecuteReader(CommandBehavior.CloseConnection)
    IF   'check if there rows return
    'has rows
    Else                   'If no row return
    'no rows
    End While

    Pls give this a try .

    Have a nice day
    LVL 3

    Expert Comment

    Use the ExecuteScalar method to call a stored procedure...then you can utilize an aggregate function to retrieve the row info you require, such as a count of rows returned by a query.  This would be much faster than the SqlDataReader approach...
    LVL 1

    Author Comment

    thanks, vinhthuy_nguyen  yes it's VB.NET I'll try this tomorrow morning


    LVL 8

    Assisted Solution

    You can make the query faster by restricting the number of rows to 1 by adding the following at the end of the query.
    WHERE RowNum < 2   ( This will return only one row even if it has many rows to return)

    As said already, if you still need to restrict it to a field, use ExecuteScalar - You should be using count(*)

    Check for yourself which quer us quicker
    Count(*) orWHERE RowNum < 2

    Count(*) will be quicker if the result set has less number of rows.
    RowNum < 2 will be quicker if the result set has less number of columns.


    LVL 1

    Author Comment

    I'm using reader and count(*) thanks!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    This video discusses moving either the default database or any database to a new volume.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    737 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

    20 Experts available now in Live!

    Get 1:1 Help Now