Need help creating a pass thru query or stored procedure to optimize
Hello.
Most of my programming experience has been with MS Access 2000 with a split FE/BE, but now we are slowly migrating to MS SQL Server, and I am a fish out of water. I suspect this is a very simple problem.
I use the following code in my MS Access application.
strSQLtext = "INSERT INTO TempEbayOutput1 SELECT qryEbayUploadOnlyPhotos2.*
DoCmd.RunSQL (strSQLtext)
When processing 11,000 records, the code below takes 15 minutes to run. All tables are now stored on the SQL Server. I'm not sure how to take advantage of SQL to optimize speed. Would like to get advice whether this should be a stored procedure or a pass thru query, and help creating such.
Note that the query, qryEbayUploadOnlyPhotos2 must be create by my code on the fly.
Thanks
Most of my programming experience has been with MS Access 2000 with a split FE/BE, but now we are slowly migrating to MS SQL Server, and I am a fish out of water. I suspect this is a very simple problem.
I use the following code in my MS Access application.
strSQLtext = "INSERT INTO TempEbayOutput1 SELECT qryEbayUploadOnlyPhotos2.*
DoCmd.RunSQL (strSQLtext)
When processing 11,000 records, the code below takes 15 minutes to run. All tables are now stored on the SQL Server. I'm not sure how to take advantage of SQL to optimize speed. Would like to get advice whether this should be a stored procedure or a pass thru query, and help creating such.
Note that the query, qryEbayUploadOnlyPhotos2 must be create by my code on the fly.
Thanks
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
as you normally would write the CSV file on the local computer, the easiest way of doing that is to simply use the query itself - there is no need to save that into a temp table so you don't need the INSERT.
You have a form for all the eBay fields, if that is bound to another table on the server then you should be able to also JOIN the tables directly on the server instead of using all these Form!... references - then you can create a view on the server with the JOINed tables and use that to create the CSV file locally.
If you have one row for all the fields of the eBay form and you want to apply that to all parts of "SelectedParts" that you can JOIN them by doing a CROSS JOIN, that will simply use all rows of one table and combine that with all rows of the other. If the eBay table has only one row by using a WHERE then the CROSS JOIN applies only the one row of the eBay table with all rows of SelectedParts. No need to use any local form references in your query as long as you save all fields of the eBay form into a table.
As the server now do the combination it should also be very fast (and even faster if you have created correct indexes on the tables).
In most cases you can always directly use a view to get the needed results instead of writing things to a temporary table. Such construct is also dangerous if you have more than one user doing the same: You must make sure that not one user overwrites things another user has written to the same table. So at least you should separate their work by having a user ID in the temp table. But best is to avoid that wherever possible.
@Pat: "The syntax differences between T-SQL and Access SQL are minimal" - that's really hard stuff...;-) Yes, both are working with SQL and using a basic standard, but there are really a lot differences in syntax, beginning with all the parantheses Access inserts into JOINs which makes them nearly unreadable to only mention one.
"A pass-through query is the equivalent of an Access query" - that's not true, the simplest difference is that it is always read-only so you normally use it rarely in form RecordSources, only if you maybe want a read-only form.
"A stored procedure is like a VBA code loop. You wouldn't write code if you could use a simple query" - sorry, but that's nonsense. A stored procedure in it's simplest form is nothing else than an updatable SELECT with parameters where you also can use ORDER BY (which should not be used in a view). Has nothing, really nothing to do with a VBA loop. You CAN of course also use loops in T-SQL but that should be avoided wherever possible, keyword is "set based".
And if you want to change data with it you can do i.e. several changes at the same time like writing an order header and order details in two tables where you can catch errors in writing the data. In Access you would save the header data when you go into the details subform and save the details row when you go into the header main form (although this is not a good example). What I want to say is: Several steps in different tables can be done and you can set it up to do all or nothing which is what's needed any time you need to do simply more than one change (one row in one table) where a view would be enough.
In the new example of the author here it's not necessary as the example above has shown. But saying "don't use SPs as they are not needed in most cases" is wrong - it really depends on what you want to do.
"The problem with action queries such as this one is that Access wraps the update in a transaction so it can give you the option of cancelling the update after it is staged but before it is committed."
An UPDATE/INSERT/DELETE is an atomic procedure - you can always only start it and wait for the result, there is no "I start the UPDATE and decide to cancel it". If you mean the Undo function(s) of Access: That's not a transaction. Access lets you edit the fields in local in-memory controls where any input is saved as long as you see the pencil in the record selector. Until you save the record on any method, nothing is done on the server except getting the next ID of an autoID column which is lost if you undo the record. So there is no difference in using a Pass-Through query which INSERTs or UPDATEs something and saving a row in a form.
"When you are updating thousands of rows, that takes a lot of memory and time."
That's right, if you do one UPDATE-command for each row. In Access forms you cannot update more than one row at the same time. If you use an INSERT...SELECT that's again atomic and you can't do anything else than waiting for the result - but here it will be fast. I don't know where you see any possibility to have "the last minute option of changing your mind".
"Access attempts to "pass through" all queries when the BE is ODBC." - again not correct: Access uses a bunch of special stored procedures to prepare the data and finally writing the data. Others are for retrieving default values and so on. You can see that if you start a SQL Server Profiler session and look what happens in the background. Moreover it needs to change the syntax of an Access SQL to be usable in T-SQL. Samples are the asterisk in Access where SQL Server expects the percent sign as wildcard character or "ALIKE" in Access which would need to be "LIKE" in SQL Server and so on so it can't simply pass-through an Access SQL query. That's simply the reason why Pass-Through-Queries exists where you have the full possibilities and the syntax of T-SQL, nothing will be changed here when it is forwarded to the server.
Cheers,
Christian
as you normally would write the CSV file on the local computer, the easiest way of doing that is to simply use the query itself - there is no need to save that into a temp table so you don't need the INSERT.
You have a form for all the eBay fields, if that is bound to another table on the server then you should be able to also JOIN the tables directly on the server instead of using all these Form!... references - then you can create a view on the server with the JOINed tables and use that to create the CSV file locally.
If you have one row for all the fields of the eBay form and you want to apply that to all parts of "SelectedParts" that you can JOIN them by doing a CROSS JOIN, that will simply use all rows of one table and combine that with all rows of the other. If the eBay table has only one row by using a WHERE then the CROSS JOIN applies only the one row of the eBay table with all rows of SelectedParts. No need to use any local form references in your query as long as you save all fields of the eBay form into a table.
As the server now do the combination it should also be very fast (and even faster if you have created correct indexes on the tables).
In most cases you can always directly use a view to get the needed results instead of writing things to a temporary table. Such construct is also dangerous if you have more than one user doing the same: You must make sure that not one user overwrites things another user has written to the same table. So at least you should separate their work by having a user ID in the temp table. But best is to avoid that wherever possible.
@Pat: "The syntax differences between T-SQL and Access SQL are minimal" - that's really hard stuff...;-) Yes, both are working with SQL and using a basic standard, but there are really a lot differences in syntax, beginning with all the parantheses Access inserts into JOINs which makes them nearly unreadable to only mention one.
"A pass-through query is the equivalent of an Access query" - that's not true, the simplest difference is that it is always read-only so you normally use it rarely in form RecordSources, only if you maybe want a read-only form.
"A stored procedure is like a VBA code loop. You wouldn't write code if you could use a simple query" - sorry, but that's nonsense. A stored procedure in it's simplest form is nothing else than an updatable SELECT with parameters where you also can use ORDER BY (which should not be used in a view). Has nothing, really nothing to do with a VBA loop. You CAN of course also use loops in T-SQL but that should be avoided wherever possible, keyword is "set based".
And if you want to change data with it you can do i.e. several changes at the same time like writing an order header and order details in two tables where you can catch errors in writing the data. In Access you would save the header data when you go into the details subform and save the details row when you go into the header main form (although this is not a good example). What I want to say is: Several steps in different tables can be done and you can set it up to do all or nothing which is what's needed any time you need to do simply more than one change (one row in one table) where a view would be enough.
In the new example of the author here it's not necessary as the example above has shown. But saying "don't use SPs as they are not needed in most cases" is wrong - it really depends on what you want to do.
"The problem with action queries such as this one is that Access wraps the update in a transaction so it can give you the option of cancelling the update after it is staged but before it is committed."
An UPDATE/INSERT/DELETE is an atomic procedure - you can always only start it and wait for the result, there is no "I start the UPDATE and decide to cancel it". If you mean the Undo function(s) of Access: That's not a transaction. Access lets you edit the fields in local in-memory controls where any input is saved as long as you see the pencil in the record selector. Until you save the record on any method, nothing is done on the server except getting the next ID of an autoID column which is lost if you undo the record. So there is no difference in using a Pass-Through query which INSERTs or UPDATEs something and saving a row in a form.
"When you are updating thousands of rows, that takes a lot of memory and time."
That's right, if you do one UPDATE-command for each row. In Access forms you cannot update more than one row at the same time. If you use an INSERT...SELECT that's again atomic and you can't do anything else than waiting for the result - but here it will be fast. I don't know where you see any possibility to have "the last minute option of changing your mind".
"Access attempts to "pass through" all queries when the BE is ODBC." - again not correct: Access uses a bunch of special stored procedures to prepare the data and finally writing the data. Others are for retrieving default values and so on. You can see that if you start a SQL Server Profiler session and look what happens in the background. Moreover it needs to change the syntax of an Access SQL to be usable in T-SQL. Samples are the asterisk in Access where SQL Server expects the percent sign as wildcard character or "ALIKE" in Access which would need to be "LIKE" in SQL Server and so on so it can't simply pass-through an Access SQL query. That's simply the reason why Pass-Through-Queries exists where you have the full possibilities and the syntax of T-SQL, nothing will be changed here when it is forwarded to the server.
Cheers,
Christian
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is a stored procedure.
It does something that is very difficult to get done in Access.
It takes a comma-delimited list of values to use in an IN clause.
Doing that in Access or in VBA is hellacious!
USE [My_database]
GO
/****** Object: StoredProcedure [dbo].[qryFlowLineReadings
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================
-- Author: Nick67
-- Create date: 16-Jan-15
-- Description: Returns FlowLine data from a comma delimited list of PieceIDs
-- ==========================
CREATE PROCEDURE [dbo].[qryFlowLineReadings
-- Add the parameters for the stored procedure here
@List nvarchar(4000) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @JobID int
declare @pieceID int
Declare @nominal nvarchar(max)
SELECT
tFLR.JobID, tFLR.ReadingID, tFLR.PieceID,
tFLR.UT1 AS [UT-1], tFLR.UT2 AS [UT-2], tFLR.UT3 AS [UT-3], tFLR.UT4 AS [UT-4],
tFLR.UT5 AS [UT-5], tFLR.UT6 AS [UT-6], tFLR.UT7 AS [UT-7], tFLR.UT8 AS [UT-8],
tPieces.SerialNumber, tPieces.PartDescription, tPieces.Manufacturer, tPieces.PartID, tPieces.TypeName,
tblInsDetails.WO#, tblInsDetails.Date, tblClients.[Client Name], tblCertResults.Unit,
dbo.ufnCriteriaByPieceType
FROM
(SELECT tblPieces.PieceID, tblPieces.SerialNumber,tbl
FROM
tblPieces LEFT OUTER JOIN
tblPartDescriptions ON tblPieces.PartID = tblPartDescriptions.PartID
tblTypes ON tblPieces.TypeID = tblTypes.TypeID LEFT OUTER JOIN
tblMakes ON tblPieces.MakeID = tblMakes.MakeID) as tPieces Inner Join
(SELECT * from tblFlowlineReadings) as tFLR On tPieces.PieceID = tFLR.PieceID Inner Join
tblInsDetails on tblInsDetails.JobID = tFLR.JobID Inner Join
tblClients on tblClients.[Client ID]= tblInsDetails.ConsigneeID Inner Join
tblCertResults on tblInsDetails.JobID = tblCertResults.jobid Inner Join
(SELECT PieceID, MAX(JobID) as TheMax from tblFlowlineReadings group by PieceID) as LastJobs
On LastJobs.TheMax = tFLR.JobID AND LastJobs.PieceID = tflr.PieceID INNER JOIN
dbo.ufnCSVtoTVP(@List) AS TheList ON tPieces.PieceID = TheList.number
END
GO
And it uses this user-defined function to breakdown the csv list
USE [my_database]
GO
/****** Object: UserDefinedFunction [dbo].[ufnCSVtoTVP] Script Date: 04/24/2015 11:00:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================
-- Author: Nick67
-- Create date: 6-Feb-15
-- Description: Function to unpack a csv to a table valued parameter
-- ==========================
CREATE FUNCTION [dbo].[ufnCSVtoTVP] (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END
GO
This UDF does something that can also be hellacious in Access. It takes the results of multiple fields and rows and concatenates them into a single string to be returned
USE [My_database]
GO
/****** Object: UserDefinedFunction [dbo].[ufnCriteriaByPieceT
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================
-- Author: Nick67
-- Create date: 19-Jan-15
-- Description: returns UT criteria
-- ==========================
CREATE FUNCTION [dbo].[ufnCriteriaByPieceT
(
-- Add the parameters for the function here
@PieceID int,
@TypeID int
)
RETURNS nvarchar(max)
AS
BEGIN
Declare @FinalString nvarchar(255)
DECLARE @imax INT
DECLARE @i INT
DECLARE @CriteriaValue float
DECLARE @CriteriaText nvarchar(255)
DECLARE @CriteriaInfo TABLE(
RowID INT IDENTITY ( 1 , 1 ),
CriteriaValue float,
CriteriaText nvarchar(255)
)
INSERT @CriteriaInfo
SELECT tblCriteria.CriteriaValue,
FROM (tblPieces INNER JOIN tblPieceCriteria ON tblPieces.PieceID = tblPieceCriteria.PieceID) INNER JOIN tblCriteria ON tblPieceCriteria.CriteriaI
Where tblPieceCriteria.PieceID = @PieceID and tblCriteria.TypeNumber = @TypeID
Order by tblPieceCriteria.PieceCrit
SET @imax = @@ROWCOUNT
SET @i = 1
WHILE (@i <= @imax)
BEGIN
SELECT @CriteriaValue = CriteriaValue,
@CriteriaText = CriteriaText
FROM @CriteriaInfo
WHERE RowID = @i
set @FinalString = isnull(@FinalString,'') + ltrim(str(@CriteriaValue,1
set @FinalString = Case when @i <> @imax then isnull(@FinalString,'') + ' / ' else isnull(@FinalString,'') end
SET @i = @i + 1
END -- WHILE
RETURN isnull(@FinalString,'')
END
GO
None of this is perhaps applicable to you, but it gives you the beginning of T-SQL to start to get your feet wet, to see what procedural T-SQL logic looks like, and to begin to rethink how you are tacking your task.
Hope it helps.
Nick67
It does something that is very difficult to get done in Access.
It takes a comma-delimited list of values to use in an IN clause.
Doing that in Access or in VBA is hellacious!
USE [My_database]
GO
/****** Object: StoredProcedure [dbo].[qryFlowLineReadings
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================
-- Author: Nick67
-- Create date: 16-Jan-15
-- Description: Returns FlowLine data from a comma delimited list of PieceIDs
-- ==========================
CREATE PROCEDURE [dbo].[qryFlowLineReadings
-- Add the parameters for the stored procedure here
@List nvarchar(4000) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @JobID int
declare @pieceID int
Declare @nominal nvarchar(max)
SELECT
tFLR.JobID, tFLR.ReadingID, tFLR.PieceID,
tFLR.UT1 AS [UT-1], tFLR.UT2 AS [UT-2], tFLR.UT3 AS [UT-3], tFLR.UT4 AS [UT-4],
tFLR.UT5 AS [UT-5], tFLR.UT6 AS [UT-6], tFLR.UT7 AS [UT-7], tFLR.UT8 AS [UT-8],
tPieces.SerialNumber, tPieces.PartDescription, tPieces.Manufacturer, tPieces.PartID, tPieces.TypeName,
tblInsDetails.WO#, tblInsDetails.Date, tblClients.[Client Name], tblCertResults.Unit,
dbo.ufnCriteriaByPieceType
FROM
(SELECT tblPieces.PieceID, tblPieces.SerialNumber,tbl
FROM
tblPieces LEFT OUTER JOIN
tblPartDescriptions ON tblPieces.PartID = tblPartDescriptions.PartID
tblTypes ON tblPieces.TypeID = tblTypes.TypeID LEFT OUTER JOIN
tblMakes ON tblPieces.MakeID = tblMakes.MakeID) as tPieces Inner Join
(SELECT * from tblFlowlineReadings) as tFLR On tPieces.PieceID = tFLR.PieceID Inner Join
tblInsDetails on tblInsDetails.JobID = tFLR.JobID Inner Join
tblClients on tblClients.[Client ID]= tblInsDetails.ConsigneeID Inner Join
tblCertResults on tblInsDetails.JobID = tblCertResults.jobid Inner Join
(SELECT PieceID, MAX(JobID) as TheMax from tblFlowlineReadings group by PieceID) as LastJobs
On LastJobs.TheMax = tFLR.JobID AND LastJobs.PieceID = tflr.PieceID INNER JOIN
dbo.ufnCSVtoTVP(@List) AS TheList ON tPieces.PieceID = TheList.number
END
GO
And it uses this user-defined function to breakdown the csv list
USE [my_database]
GO
/****** Object: UserDefinedFunction [dbo].[ufnCSVtoTVP] Script Date: 04/24/2015 11:00:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================
-- Author: Nick67
-- Create date: 6-Feb-15
-- Description: Function to unpack a csv to a table valued parameter
-- ==========================
CREATE FUNCTION [dbo].[ufnCSVtoTVP] (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END
GO
This UDF does something that can also be hellacious in Access. It takes the results of multiple fields and rows and concatenates them into a single string to be returned
USE [My_database]
GO
/****** Object: UserDefinedFunction [dbo].[ufnCriteriaByPieceT
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================
-- Author: Nick67
-- Create date: 19-Jan-15
-- Description: returns UT criteria
-- ==========================
CREATE FUNCTION [dbo].[ufnCriteriaByPieceT
(
-- Add the parameters for the function here
@PieceID int,
@TypeID int
)
RETURNS nvarchar(max)
AS
BEGIN
Declare @FinalString nvarchar(255)
DECLARE @imax INT
DECLARE @i INT
DECLARE @CriteriaValue float
DECLARE @CriteriaText nvarchar(255)
DECLARE @CriteriaInfo TABLE(
RowID INT IDENTITY ( 1 , 1 ),
CriteriaValue float,
CriteriaText nvarchar(255)
)
INSERT @CriteriaInfo
SELECT tblCriteria.CriteriaValue,
FROM (tblPieces INNER JOIN tblPieceCriteria ON tblPieces.PieceID = tblPieceCriteria.PieceID) INNER JOIN tblCriteria ON tblPieceCriteria.CriteriaI
Where tblPieceCriteria.PieceID = @PieceID and tblCriteria.TypeNumber = @TypeID
Order by tblPieceCriteria.PieceCrit
SET @imax = @@ROWCOUNT
SET @i = 1
WHILE (@i <= @imax)
BEGIN
SELECT @CriteriaValue = CriteriaValue,
@CriteriaText = CriteriaText
FROM @CriteriaInfo
WHERE RowID = @i
set @FinalString = isnull(@FinalString,'') + ltrim(str(@CriteriaValue,1
set @FinalString = Case when @i <> @imax then isnull(@FinalString,'') + ' / ' else isnull(@FinalString,'') end
SET @i = @i + 1
END -- WHILE
RETURN isnull(@FinalString,'')
END
GO
None of this is perhaps applicable to you, but it gives you the beginning of T-SQL to start to get your feet wet, to see what procedural T-SQL logic looks like, and to begin to rethink how you are tacking your task.
Hope it helps.
Nick67
ASKER
Thanks everyone. I'm still working on this and will update you soon.
ASKER
The purpose of this query is simply to create a temporary table that can be used to upload content to eBay, using the eBay required field names. Once the table is created, my code will create a csv file which is what I ultimately need.
My VBA code must build a query that populates the table with only the fields I need to upload. This is why the query is created programmatically. For example, lets say we only wish to revise our ebay titles, then the query will be different that if we want to revise the title and price.
I'm hoping someone can show me how to write vba code that will include the insert for the SIMPLE QUERY shown below. If I can get this working I'm sure I can figure out the rest.
I don't think I've ever created a pass through query in code before, so this will be my first attempt. :-(
Thanks again
TempDistQuery.txt
tempdistquerysimple.txt