Fastest SQLServer 2000 query

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
LVL 1
TrancedifiedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vinhthuy_nguyenCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GrnEggsAndHamCommented:
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
TrancedifiedAuthor Commented:
thanks, vinhthuy_nguyen  yes it's VB.NET I'll try this tomorrow morning

GrnEggsAndHam,

Examples?
0
rajaloysiousCommented:
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
TrancedifiedAuthor Commented:
I'm using reader and count(*) thanks!

Chris
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.