Recordset filters - Access vs Sql server

[ranne]

Hi!

I am working on a program in vb6. When I apply filters to a recordset from an access database the filters are relatively quick, but when I open a recordset from the same database on sql server the filters are extremely slow. Does anyone know the reason for this? The recordsets have the same number of records. Any help would be greatly appreciated!

 

[Homersim2600]

SQL is a big memory hogger. I have had similar problems with SQL myself and have since abandoned it for MySQL which is so much faster. You should also take into account the traffic on your network, and the amount of records in your database as these two things could significantly slow down the process.

"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein

 

[ranne]

So other than switching databases , what would be a way to remedy this? The database is very large (~4 million points), but the queries to sql server are relatively quick. It's just once I get the recordset back into vb, I search the recordset using a bunch of filters and it takes a long time. Is there a better/faster way to extract rows from a recordset?

 

[gmmastros]

You could try letting the database do the filtering for you. Add a where clause to your select.

 

[stoolpigeon]

You could always just do the filtering with the query that you pull into the recordset-- rather than pulling more data than you need. Or dump the recordset into an array and then work on that.

 

[ranne]

Thanks for the ideas. Since I need to have the database in both Access and sql server, I want basically the same code for each situation. The reason that I take in a larger recordset (2000 points) is that I need to find about 25 points that have varying criteria depending on where they are located. I found that running more specific queries in access slowed it down a lot as opposed to the larger recordset/filter method. I think I'll give the array method a shot...

 

[bjd4jc]

Do you have the tables indexed appropriately in both databases?

 

[ranne]

Not sure. In sql, I have an id column primary key, another index for the x & y columns and a 3rd index for the 5 columns that make up the location. In access, there are the same columns but they are indexed individually.

Is there a better way to index them? I need to query the database twice. The first query finds a minimum distance to the x,y, and the second searches based on the 5 location columns.

 

[ArtieChoke]

Depending on how you set up your recordset, you may not have everything in memory, so it keeps going back to the server during the filtering. Try a client-side cursor so all the records get pulled over to your program.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[johnwm]

When you say you need to have the same database in SQL and in Access, could you use MSDE instead of Access? You could then optimise for SQL, including using proper queries and stored procedures, and use exactly the same code on MSDE (presumably for local copies or laptops)

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[ranne]

Thanks! A client-side cursor did the trick. I'll have to ask about switching to MSDE because speed is still an issue and Access has been nothing but problems. Thanks again.