May 10, 2010 at 11:54 am
HI
i migrated access database to SQL using up sizing wizard. and linked tables to SQL server
all seems to be fine except that when we do search using the ctrl+f method, it takes long time to find the record.
the primary key is set on a Tracking NO which is unique. but we search by customer Branch no. which is not unique or could be duplicate.
the profiler reports that every time we search it goes through all tracking NO.
any help would be appreciated.
Thank you
May 10, 2010 at 11:58 am
On Sql server an index should be added on the field you are doing the search on. This may not alone be enough to speed up the entire search but is likely part of the problem. If you are new to SQL I would suggest reading up on Query analyzer which could suggest the correct sequence of indexs that would ultimatley speed up performance.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 10, 2010 at 12:01 pm
thank you
i have an index on the field. from SQL.
or should i put on the local table on Access as well,.
May 10, 2010 at 12:04 pm
in access you would actually need to go to the linked tables manager and update that linked table. That should add the index from SQL.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 10, 2010 at 12:11 pm
ok
did that..
but still no difference.
does it take time to take effect. it's been more than 24 hrs.
May 10, 2010 at 12:18 pm
No it does not take any time. Ultimatley I would advise moving away from Access all together if possible. we have one ourselves that I can not get the correct people engaged in so I can get rid of it and it functions as well as I can get it too but it is still far slower than accessing the database directly. The very way in which the find functions works in Access likely requires that a table scan be done. I would try to build a query agaisnt that table. If the Query run against that table is fast then the problem is with the find function and access itself.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 10, 2010 at 12:26 pm
what would be the other option other than access mdb file.?
May 10, 2010 at 12:32 pm
I assume at this point you are using the Access database as a front end. The data resides on SQL but you are using Access for the forms and reports that the front end user can access.
The option is to replace the front end. This means creating a user interface from something like .net
to replace the forms.
If you have SQL 2005 the options and features of SQL server reporting services are more robust then anything you will ever find in Access.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 10, 2010 at 12:49 pm
I would suggest you get the execution plan for the query and take a look at it. You're probably getting a table/clustered index scan, or possibly and index scan, instead of an index seek. That could be because of something in the code, say a function on the column in the WHERE clause, or because the data is not selective enough to make the index you created on it useful.
Without seeing the query or the execution plan, it's hard to know for sure.
To get the execution plan, you can use either a server side trace or dynamic management views. Since you're just getting started, I'd suggest the DMV approach. It'll probably be easier.
SELECT * FROM
sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.text LIKE 'SELECT...%'
You'll have to supply the right values for the LIKE statement.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 11, 2010 at 12:26 pm
well,
i added indexes. i ran SQL profiler, and imported trace file into Engine Tuning Advisor. which advised me to create more indexes and statistics.
i have attached a copy or the trace file. so you can take a look at it.
its weired that when we search using Ctrl+F it scans each record till it finds the target.
Thank you
May 11, 2010 at 1:34 pm
Do you have a single index which covers both Tracking NO and Branch NO?
May 11, 2010 at 1:53 pm
No
i have the following indexes
1 Clustered on store NO
1 Non-Clustered for Tracking No
most of the search we do is by Store NO...
but i will do that and let you know
May 11, 2010 at 1:54 pm
should i include Store NO in the index Key Column
and include Tracking NO in the Included Column
May 11, 2010 at 2:18 pm
i just added an index that includes Tracking NO & Store NO
that screwed my History notes which i related to the another table that uses the same Tracking NO to view Information"
May 11, 2010 at 2:37 pm
if your looking for performance then simple DON'T use an Access front end!!
Whilst Access will process as much of the query as possible on SQL Server most times the data set is filtered locally, this hits network traffic and machine performance. There are some do's and don'ts to ensure you get the max from your Access front end. You should research and review these against your current set up!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply