November 12, 2010 at 8:45 am
I am converting an Access db to SQL Server. In many cases I am a bit fuddled on the appropriate times to use a view, stored procedure or UDF. Some of it has become clearer after lots of reading and experimenting, but this one has me at a bit of a loss.
I am forced to do a join between a local table and a SQL Server table, of about 100 and 80,000 records, respectively. (The local table is simply a list of AutoNumber primary keys, created by cumulative selecting and filtering by the user, and I know of no easy way to make that happen for each user on the server.)
I created two queries on the server, one a view and one a stored procedure, completely identical, except for the required ballast surrounding a stored procedure, added ODBC links to each one in the Access front end and made two local Access queries, identical, except one joins the local table to the view, the other to the stored procedure. The difference in execution time is phenomenal - virtually instantaneous for the view and around 15 seconds for the stored procedure.
Is this normal? I haven't run across anything that would indicate there should be such a huge difference between the two methods. Can anyone tell me what's going on and if I can predict such behavior in future cases, so I don't have to always construct tests?
Pete
November 16, 2010 at 9:38 am
What does the SP look like? Are you passing in the IDs as a parameter or doing a jion to the resultset?
If the former then access will be calling the SP for each id.
If the later then access is probably retrieving the full resultset then doing the join.
You could create a temp table, populate with the IDs then join to it in the SP.
Another option is to hold a table on the server with the user as a column, populate it rather than the local table then call the sp with the user as a parameter to join to the table.
It would also probably need a bit of housekeeping.
Cursors never.
DTS - only when needed and never to control.
November 16, 2010 at 12:52 pm
No parameters, selecting from the 80,000 records in the SQL table is done by joining to a local table. This project started life as an Access database, so there is quite a bit of code that does not take any account of network traffic, hence the join between local and remote table. The Access query looks like this:
SELECT spPodrobnostiProTiskKomurek.*
FROM spPodrobnostiProTiskKomurek INNER JOIN PodrobnostiProTisk ON spPodrobnostiProTiskKomurek.PodrobnostiAutoID = PodrobnostiProTisk.PodrobnostiAutoID;
or like this:
SELECT vwPodrobnostiProTiskKomurek.*
FROM vwPodrobnostiProTiskKomurek INNER JOIN PodrobnostiProTisk ON vwPodrobnostiProTiskKomurek.PodrobnostiAutoID = PodrobnostiProTisk.PodrobnostiAutoID;
depending on whether I try to join to the procedure or the view. I was tempted to try storing the current local table data in some form on the server as well, but the table is created locally by the user selecting various filtering criteria, so I thought it would be faster and certainly easier to store the selected numbers locally, then do one join at the end, rather than sending to the server lists of potentially tens of thousands of ID numbers, then by steps whittling them away. I understand that joins between local and remote tables are generally to be avoided, but this does work, very nicely. I don't know how, but Access and SQL Server manage to communicate the necessary information to get the results of the join with -no- perceptible delay, as long as I join to the view. When I join to the stored procedure, which to me looks identical, the performance drops from instant to around fifteen seconds. Would the query plan help in tracking down what's happening?
Here is the stored procedure:
ALTER PROCEDURE [dbo].[spPodrobnostiProTiskKomurek]
AS
BEGIN
SET NOCOUNT ON;
SELECTPodrobnosti.PodrobnostiAutoID,
IsNull(AkcesitPred + '-','') + Cast(Akcesit As varchar(6)) + '/' + Cast(Rok As varchar(4)) Akc,
IsNull(EvidenceLetter + ' ' + Cast(EvidenceNumber As varchar(5)),'') + Case When Len(evidenceextra)>0 Then ' (' + EvidenceExtra + ')' Else '' End DrEvidence,
TableOfGenuss.Genus,
TableOfSubGenuss.SubGenus,
TableOfSpeciess.Species,
TableOfSubSpeciess.SubSpecies,
TableOfSystems.System,
TableOfLithographicUnits.LithographicUnit,
TableOfLithographicSubUnits.LithographicSubUnit,
TableOfZones.Zone,
TableOfOrigins.Origin,
Podrobnosti.Original,
Podrobnosti.Authors,
Podrobnosti.Lokalita
FROM (((((((((Akces
INNER JOIN Podrobnosti ON Podrobnosti.AkcesAutoID = Akces.AkcesAutoID)
LEFT JOIN TableOfGenuss ON Podrobnosti.GenusAutoID = TableOfGenuss.GenusAutoID)
LEFT JOIN TableOfSpeciess ON Podrobnosti.SpeciesAutoID = TableOfSpeciess.SpeciesAutoID)
LEFT JOIN TableOfSubGenuss ON Podrobnosti.SubGenusAutoID = TableOfSubGenuss.SubGenusAutoID)
LEFT JOIN TableOfSubSpeciess ON Podrobnosti.SubSpeciesAutoID = TableOfSubSpeciess.SubSpeciesAutoID)
LEFT JOIN TableOfOrigins ON Podrobnosti.OriginAutoID = TableOfOrigins.OriginAutoID)
LEFT JOIN TableOfLithographicUnits ON Podrobnosti.LithographicUnitAutoID = TableOfLithographicUnits.LithographicUnitAutoID)
LEFT JOIN TableOfLithographicSubUnits ON Podrobnosti.LithographicSubUnitAutoID = TableOfLithographicSubUnits.LithographicSubUnitAutoID)
LEFT JOIN TableOfZones ON Podrobnosti.ZoneAutoID = TableOfZones.ZoneAutoID)
LEFT JOIN TableOfSystems ON Podrobnosti.SystemAutoID = TableOfSystems.SystemAutoID
WHERE TableOfGenuss.Genus Is Not Null
ORTableOfSubGenuss.SubGenus Is Not Null
ORTableOfSpeciess.Species Is Not Null
ORTableOfSubSpeciess.SubSpecies Is Not Null
ORTableOfSystems.System Is Not Null
ORTableOfLithographicUnits.LithographicUnit Is Not Null
ORTableOfLithographicSubUnits.LithographicSubUnit Is Not Null
ORTableOfZones.Zone Is Not Null
ORTableOfOrigins.Origin Is Not Null
ORPodrobnosti.Original Is Not Null
ORPodrobnosti.Authors Is Not Null
ORPodrobnosti.Lokalita Is Not Null
END
and here is the view:
SELECT dbo.Podrobnosti.PodrobnostiAutoID, ISNULL(dbo.Akces.AkcesitPred + '-', '') + CAST(dbo.Akces.Akcesit AS varchar(6))
+ '/' + CAST(dbo.Akces.Rok AS varchar(4)) AS Akc, ISNULL(dbo.Podrobnosti.EvidenceLetter + ' ' + CAST(dbo.Podrobnosti.EvidenceNumber AS varchar(5)),
'') + CASE WHEN Len(evidenceextra) > 0 THEN ' (' + EvidenceExtra + ')' ELSE '' END AS DrEvidence, dbo.TableOfGenuss.Genus,
dbo.TableOfSubGenuss.SubGenus, dbo.TableOfSpeciess.Species, dbo.TableOfSubSpeciess.SubSpecies, dbo.TableOfSystems.System,
dbo.TableOfLithographicUnits.LithographicUnit, dbo.TableOfLithographicSubUnits.LithographicSubUnit, dbo.TableOfZones.Zone,
dbo.TableOfOrigins.Origin, dbo.Podrobnosti.Original, dbo.Podrobnosti.Authors, dbo.Podrobnosti.Lokalita
FROM dbo.Akces INNER JOIN
dbo.Podrobnosti ON dbo.Podrobnosti.AkcesAutoID = dbo.Akces.AkcesAutoID LEFT OUTER JOIN
dbo.TableOfGenuss ON dbo.Podrobnosti.GenusAutoID = dbo.TableOfGenuss.GenusAutoID LEFT OUTER JOIN
dbo.TableOfSpeciess ON dbo.Podrobnosti.SpeciesAutoID = dbo.TableOfSpeciess.SpeciesAutoID LEFT OUTER JOIN
dbo.TableOfSubGenuss ON dbo.Podrobnosti.SubGenusAutoID = dbo.TableOfSubGenuss.SubGenusAutoID LEFT OUTER JOIN
dbo.TableOfSubSpeciess ON dbo.Podrobnosti.SubSpeciesAutoID = dbo.TableOfSubSpeciess.SubSpeciesAutoID LEFT OUTER JOIN
dbo.TableOfOrigins ON dbo.Podrobnosti.OriginAutoID = dbo.TableOfOrigins.OriginAutoID LEFT OUTER JOIN
dbo.TableOfLithographicUnits ON dbo.Podrobnosti.LithographicUnitAutoID = dbo.TableOfLithographicUnits.LithographicUnitAutoID LEFT OUTER JOIN
dbo.TableOfLithographicSubUnits ON
dbo.Podrobnosti.LithographicSubUnitAutoID = dbo.TableOfLithographicSubUnits.LithographicSubUnitAutoID LEFT OUTER JOIN
dbo.TableOfZones ON dbo.Podrobnosti.ZoneAutoID = dbo.TableOfZones.ZoneAutoID LEFT OUTER JOIN
dbo.TableOfSystems ON dbo.Podrobnosti.SystemAutoID = dbo.TableOfSystems.SystemAutoID
WHERE (dbo.TableOfGenuss.Genus IS NOT NULL) OR
(dbo.TableOfSubGenuss.SubGenus IS NOT NULL) OR
(dbo.TableOfSpeciess.Species IS NOT NULL) OR
(dbo.TableOfSubSpeciess.SubSpecies IS NOT NULL) OR
(dbo.TableOfSystems.System IS NOT NULL) OR
(dbo.TableOfLithographicUnits.LithographicUnit IS NOT NULL) OR
(dbo.TableOfLithographicSubUnits.LithographicSubUnit IS NOT NULL) OR
(dbo.TableOfZones.Zone IS NOT NULL) OR
(dbo.TableOfOrigins.Origin IS NOT NULL) OR
(dbo.Podrobnosti.Original IS NOT NULL) OR
(dbo.Podrobnosti.Authors IS NOT NULL) OR
(dbo.Podrobnosti.Lokalita IS NOT NULL)
November 26, 2010 at 4:19 pm
I've done a lot of Access/SQL Server development. I avoid joining a local Access table to a SQL Server table unless the Access table has only a couple of records. I've found it to be very slow in most cases.
There are a couple of ways to handle this sort of thing. If only 1 user can do this at a time, then you can pump the local Access table of keys into a SQL Server table and use that in a stored procedure to to join to the SQL Server table.
If more than 1 user can do this at the same time, then you can build a string of delimited key values and pass it to the stored procedure. The stored procedure would then break the string out into a temp table of individual keys and use that to join to the main table.
Jeff Moden has a very good table valued function that will split a string into a table. I think it is called Delimited8KSplit. It should be easy to find on this site.
Todd Fifield
April 26, 2011 at 3:34 pm
(I see this is stale, but I was surprised that no one had responded)
That's normal, based on my experience. Joining to a view allows a more efficient breakdown of statements that are actually sent to the SQL Server. I'm not sure if it's a function of Access or of the ODBC driver, but I've looked at traces of what gets sent to SQL Server on some different queries sent from Access. I was suprised at the extent to which the statements were broken down. My example showed the creation and drop of stored procedures that were used to bring the data back.
Joining to a view allows for this optimization. However, joining to a stored procedure would prevent that because it would be crazy (and likely impossible) for an optimizer to try to determine the guts of a stored procedure and adjust statements for a more efficient join. However, when using a view, the default behavior of SQL Server (as far as I have noticed) is to covert the statements to be the most efficient for the minimal parts needed from the source tables.
Joining Access data to a stored procedure will either require all of the stored procedure results to be pulled to the local PC running Access, or potentially even make multiple calls to the stored procedure to return the correct results.
I have almost exclusively used linked tables to view or tables when using Access as a front-end for SQL Server data.
I've gotten a lot of helpful information about Access and Access with SQL Server from Tony Toews, and Access MVP. Here two links to his very minimalistic web page:
http://www.granite.ab.ca/accsmstr.htm "Tony's Main Microsoft Access Page"
http://www.granite.ab.ca/access/sqlserverupsizing.htm "My random thoughts on SQL Server Upsizing from Microsoft Access"
Edit:
:pinch: OK, so others have replied... (I've got to get used to this forum layout)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply