Access to SQL Server

[larrykl]

Hi all...
I convert my ASP web application to SQL Server because my database got TOO big and my number of connections got to many due to the number of users trying to access the site.
I was connecting to Access using a DSN-less connection via ADODB and I'm doing the same now to connect to SQL Server.
For some reason when I run the same script connected to Access, it runs much faster than when its connected to SQL Server.

The script does a SELECT on a large table retrieving all records and then loops through them. For each record in the loop it does another SELECT.

Any ideas?

Thanks in advance....
Larry

[txbakers]

hi Larry and welcome to WWW.

I was also using ACCESS with DSN, switched to DSN-less, but ran into all kinds of errors so went back to DSN. I think Access should stick with DSN.

SQL server could need some optimizing.

I'm in the process of switching all my Access tables to mySQL for the same reasons - lots more traffic, bigger files, etc.

[larrykl]

Hi txbakers,

I'm having no problems with DSN-less connections with Access... I actually read that it is usually faster than using DSN.

I can't figure out why simple SELECTs would be slower with SQL Server though.

This is a huge problem for me because I need to switch to SQL Server due to the high number of connections that are being attempted from my increased user count. Access has trouble with this. It causes big slow downs. Now, if I go to SQL Server, it can handle the connections but the queries are slow.

TERRIBLE! I don't know what to do.
Any ideas?

Thanks....
Larry

[aspdaddy]

Hi txt,
Why do you say Access should use dsn, What problems did you have? I think access should use dsn-less always, as long as you can set the security on the folder that the db is in.

The reason for the slow query in the original post could be many reasons. maybe there is a bad route between IIS & SQLServer?
Are you using this in the conection?
Provider=SQLOLEDB;Network=dbmssocn;

[larrykl]

Hi,

I'm connecting to the SQL Server like this:

Set DB = Server.CreateObject("ADODB.Connection")

ConnStr = "DRIVER={SQL Server};SERVER=server_name;UID=user_id;PWD=password;DATABASE=db_name"

Any ideas?
Thanks...
Larry

[aspdaddy]

Try it like this:

Provider=SQLOLEDB;
Data Source=server_name;
Initial Catalog=database_name;
User ID=username;
Password=password;
Network=dbmssocn

[larrykl]

Hi,

I'm having trouble with the syntax trying to do what you suggest.
How would I replace what I currently have to what I should have:

Set DB = Server.CreateObject("ADODB.Connection")

ConnStr = "DRIVER={SQL Server};SERVER=server_name;UID=user_id;PWD=password;DATABASE=db_name"

Thanks for all the help....
Larry

[larrykl]

When I try to use:

Set DB = Server.CreateObject("SQLOLEDB")

I get:
Error Type:
Server object, ASP 0177 (0x80004002)
No such interface supported

[larrykl]

Ooops...

figured it out. But STILL slow.
I think this problem may be that in the transfer from Access, there really aren't any idexes other than the primary key. Maybe I need to define some now? Also, maybe I need to use T-SQL, views and stored procedures?
I still don't understand why the same script would be so slow running SQL Server though. Should still be close to the Access driver, shouldn't it?

Thanks in advance...
Larry

[tomasz]

If you do multiple selects inside of the loop you need to optimize your query and maybe see if you can create store procedure. I don't think it is connection unless you are openning and clossing your connection inside your loop, It looks like your query needs to be optimized.

[larrykl]

I started trying to switch my database from Access to SQL Server 2000 because my site was getting bogged down by too many users.
My Host kept telling me that the problem was that Access can't handle that many concurrent users.
Everyone of my ASP pages kills all connections at the end of the page load. Isn't it true that if the page scripts are short, then there should be relatively low occurrence of problem since connections are opened and closed frequently?
I ended up downloading my Access database, doing a Compact on it and re-uploaded it. All of my slow downs went away which tells me that the slow downs had nothing to do with connections to Access!

I do realize that as my user base grows, I'll need to switch to SQL Server but before I do that, I'll need to implement stored procedures as much as possible.

Thoughts?
Larry

[aspdaddy]

I ended up downloading my Access database...

You can actually do this online with asp, using the Compact method on the JRO.JetEngine object

[tomasz]

I believe Access does not supports store procedures and if you really want to take advantage of them you have to switch to SQL server.

<quote>
My Host kept telling me that the problem was that Access can't handle that many concurrent users.
</quote>

Yes your provider is correct Access only supports 10 concurrent connections, but that should not be case since connection is usually terminated at page level and ties only one connection.

[larrykl]

Thanks aspdaddy and tomasz!

I'm actually aware of the ASP code to compact the database online... I've tried it out. Seems to work great.
I have a couple of questions about this though...
I understand that when you have quite a few records and then you delete a big chunk in the middle, that space is not cleaned up until you compact. When that space is cleaned up, are the primary keys or indexes renumber to consecutive numbers? If so, how do I avoid referencing problems where I have a field value that is the primary key in one table as an entry in other table? Basically the question is does compacting change ANY field value data including those fields that are keys, indexes, etc?
The other question is when I compact via an ASP script, do I have to make sure that no users try to access the database or make connections during that time?

tomasz, are you saying that while my Host is correct about the number of connection to Access, you agree with me that the number of connections was not really my problem?

Thanks for all of the excellent help....
Larry

[cyril kearney]

Access does not have stored procedures in the same sense as SQL Server. But in a more limited sense an Access Query is a stored procedure.

Primary Key are not renumbered when an Access database is compacted. That would cause chaos. The data is stored on the disk drive in a more compact fashion so it takes up less space.

Usually when I think of Access, I think about 5-8 simultaneous users beyond that I think you'll want to use SQL Server. It is important to compact your database and recreate your indexes when you have a lot of changes. What is a lot? Say 15% of the records being added or deleted. If things are slow on Access, you might also revisit your database design and try to optimize that.

[Slade]

I've seen access handle as many as 50 connections without too much issue(it was slow, but no issue). And this was a 600MB DB. It will, if you be nice to it...

Just out of curiosity, are all your selects this slow, or just some of them?

You're not doing 'SELECT *' are you?

Yes, indexing will make a difference.

[txbakers]

I've seen more than one person advise against SELECT *. Why is that?

[larrykl]

Hi...

I've narrowed down the query that is causing my slow-down on some pages.

I'm doing a SELECT COUNT(*).

I have the same content in an Access DB and in an SQL Server DB.
When I do the above query with the COUNT(*) function, the SQL Server is INCREDIBLY slow! I'm doing the select on a fairly large table with over 100K records.
I'm sure it would be better to do the count with a stored procedure in SQL Server, but a new to SQL Server so I haven't learned how to implement a stored procedure yet and access the result through my ASP.

Ideas about the slowness?

Thanks for all the great help...
Larry

[aspdaddy]

50 concurrent users? I would drop access if I expect more than about 3!

txt, select * takes two trips - one for the fieldnames, one for the data.

[tomasz]

Select count(*) is not really efficient way to get count
try

SELECT Count(0) AS myCount

this query will be faster