Slow response after move to SQL back end

[Rod]

I recently moved my access database from one which used tables linked to a
back end access file to one which is linked to an sql back end. I am using a
machine dsn which I am setting up on each machine that manages the data.
Suddenly it seems that the response is extremely slow when it didn't seem to
be a problem in the past

 

[Stefan Hoffmann]

hi Rod,

I recently moved my access database from one which used tables linked to a
back end access file to one which is linked to an sql back end. I am using a
machine dsn which I am setting up on each machine that manages the data.
Suddenly it seems that the response is extremely slow when it didn't seem to
be a problem in the past

Just migrating the BE to SQL Server doesn't improve performance. When
you have a bad normalized data model then you may notice some kind of
performance collapse.


mfG
--> stefan <--

 

[Rod]

I didn't expect it to improve performance, it actually got worse. How do I
know what a bad normalized data model is?

 

[Stefan Hoffmann]

hi Rod

I didn't expect it to improve performance, it actually got worse. How do I
know what a bad normalized data model is?

http://en.wikipedia.org/wiki/Database_normalization

You normally have to migrate more or less queries to SQL Server views.


mfG
--> stefan <--

 

[Tom W]

WRONG

moving to SQL Server _DOES_ increase performance-- if you do it correctly

linked tables are not reccomended


you need to move your application to ADP

 

[Tom W]

and / or sprocs

it's really not that hard I had to do 200 queries in a week a couple of
months back

 

[Paul Shapiro]

Access automatically creates hidden indexes on all Foreign Key fields, on
the grounds that it usually helps performance. SQL Server does NOT
automatically create any indexes. Depending on how you moved your db to SQL,
you might consider if you need any additional indexes. For example, if you
had a Student table and an Enrollment table in a 1:Many relationship, Access
would make an index on Enrollment.studentID. SQL Server would probably
benefit if you create that same index, since it leaves it up to you. In SQL
Server you have the additional option of making it a clustered index,
meaning the Enrollment data would be physically stored in studentID order.
If most of your retrievals were for all the enrollments of a given student,
this could be a substantial performance increase if the tables are large
enough.

 

[IRS Intern]

I fully agree

I just reccomend the usage of either 'Index tuning wizard' or
'database tuning advisor'