[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Fastest SQLServer 2000 query

Posted on 2004-11-08
5
Medium Priority
?
164 Views
Last Modified: 2010-04-23
Hello,

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
Else
'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?

Thanks!

Chris Chang
0
Comment
Question by:Trancedified
5 Comments
 
LVL 4

Accepted Solution

by:
vinhthuy_nguyen earned 1000 total points
ID: 12530017
Hi,
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
       MySQLCommand.Parameters.Add(parameter)
 Next


'.... pls use your SQLCommand
dim result as SQLDataReader
dim Details as String
result = MySQLCommand.ExecuteReader(CommandBehavior.CloseConnection)
IF result.read()   '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
0
 
LVL 3

Expert Comment

by:GrnEggsAndHam
ID: 12530133
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...
0
 
LVL 1

Author Comment

by:Trancedified
ID: 12530507
thanks, vinhthuy_nguyen  yes it's VB.NET I'll try this tomorrow morning

GrnEggsAndHam,

Examples?
0
 
LVL 8

Assisted Solution

by:rajaloysious
rajaloysious earned 1000 total points
ID: 12531913
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.

Cheers



0
 
LVL 1

Author Comment

by:Trancedified
ID: 12541271
I'm using reader and count(*) thanks!

Chris
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

825 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