Need a Query that is faster! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need a Query that is faster!

Author

Topic

Haarish
Starting Member

30 Posts

Posted - 2009-06-25 : 02:18:06

Hi All,

I have an Excel VBA code through which I select strings from one particular column in a datasheet, check if it exists in a SQL Server 2008 table.

I have accompished this with the query
RS.Open "SELECT * FROM <table> WHERE <column> = '" & <CellValue> & "'", Con

but I have around 17000 rows in the sheet and checking with the above query is taking a really long time since the underlying DB table has around 20 million rows. Is there an easier and quicker way to get this done?

Thanks,
Haarish.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 02:48:30

Don't use a recordset for this.

Use the existing connection object and execute as scalar.

cn.Execute "IF EXISTS (SELECT * FROM <table> WHERE <column> = '" & <CellValue> & "' RETURN 0 ELSE RAISERROR('Record not found.', 16, 1)", , adExecuteNoRecords)

If cn.Error.Count = 0 Then
Print "Record found!"
Else
Print "Record not found."
cn.Errors.Clear
End If

Also, you haven't explained if you want a record-by-record result, or a total result.

E 12°55'05.63"
N 56°04'39.26"

Haarish
Starting Member

30 Posts

Posted - 2009-06-25 : 03:00:47

Thanks Peso. If the value is found in the DB then I will highlight that row with a different color, so I need a row by row result. I will try your suggestion to see the difference.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 03:05:58

You still would need 17,000 calls to the database.

I would redesign and try to have as few calls to the database as possible.

E 12°55'05.63"
N 56°04'39.26"

Haarish
Starting Member

30 Posts

Posted - 2009-06-25 : 03:09:55

I have changed the code to sort the column, added a check to see if the cell value is the same as the cell exactly above. If its the same then skip querying the database, else do the normal sequence operations. How does that sound considering that I could have duplicates?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 03:34:00

How many records are duplicate out of the 17,000 records?

E 12°55'05.63"
N 56°04'39.26"

Haarish
Starting Member

30 Posts

Posted - 2009-06-25 : 03:46:51

I have something around 700 records that are duplicates. So even then I make 16300 queries to the table :(!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 04:00:49

I think your best bet would be to write a macro that does this:

1. Concatenate all 17,000 records as an XML formatted string, or normal delimited string.
2. Pass the concatenated string as a parameter to a stored procedure.
3. The stored procedure checks with LEFT JOIN or NOT EXISTS to the table with 24,000,000 records if value matches.
4. The stored procedure returns a resultset of 17,000 records containing 1 or 0 (or similar).
5. The macro uses rs.GetString and paste the result into the Excel sheet.

This way there is only one call to the database.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources