August 18, 2011 at 8:31 am
I've got a speed issue with a query in Access. I'm not even sure on whether this IS normal or not.
The structure of the DB is:
• 2 ddbb's in SQL server (DB1 + DB2) working against Access front-end
• In Access, a form that is populated by different tables/queries, one that generates the problem (let's call it 'view1').
• 'view1' is a 'table' linked from SSIS.
Summarising: SSIS with 2 databases + 1 view of a table >>> connected to >>> Access front-end
Now, view1 is a view in DB2 generated using:
A combination of: an OUTER APPLY + a programmed function that reads content from DB1.
I think the use of a function may slow down the whole thing. Is there any way to speed this up?
I can only think of Indexes (but don't know how to use them) or redesigning the function (a bit scary, though). Any ideas?
August 18, 2011 at 8:40 am
Looks like you forgot to finish your post...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 18, 2011 at 8:43 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2011 at 5:52 am
I'll happily do all you ask for, but there's problems:
-Don't know what an execution plan is.
-Too many tables, functions, a backend, a frontend,...
-And the constraint of some information being confidential.
As a newbie, all I'd like is some advice on general rules to make views quicker (i.e, how to use indexes) or methods that are easy and usualy effective (maybe rebuild indexes? drop fields that are not used in a view? I dont know!).
> DB Structure:
• SSIS: 2 separate ddbb's, DB1 + DB2. DB1 gets some info from DB2 by using functions (a couple attached) and views that rely on those functions (def of one attached).
• Main view in DB1 is attached (vwVolunteerList), is linked to Access and used by the front-end to show info on a rather complex form.
• Access: usual db, linked with ODBC and with different forms.
Attached are the views/tables definitions and functions scripts.
August 19, 2011 at 6:00 am
a_ud (8/19/2011)
I'll happily do all you ask for, but there's problems:-Don't know what an execution plan is.
Did you read the article?
-Too many tables, functions, a backend, a frontend,...
For one query?
If it's that involved you'll probably need to hire someone. It'll be too much for volunteers to be happy to tackle.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2011 at 8:00 am
Read the SQL Stairways[/url] article about indexes. This topic is entirely too big to say the best practices are "..."
There have been mountains of books of written on the topic. The article referenced above is a great reference to indexes. You will notice there are 15 articles in succession about indexes. I would say read those articles, understand the concepts and do your best to apply what you learn. Then come back and ask questions about specifics as you run across them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2011 at 8:27 am
Thanks Sean.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply