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.* FROM 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
pcalabriaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I had read up this once, and the "solution" is to create (on the fly) a querydef, set that one as passthrough, and run it from code, instead of the RunSQL which will "fetch" the data from the server to the client to pass it then back for the insert.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
If qryEbayUploadOnlyPhotos2 is a query in your frontend, it won't work, as SQL Server knows nothing about it.

If all the tables used by qryEbayUploadOnlyPhotos2 exist on the server, you will have to modify the SQL of this to fit the syntax of the server and send the full SQL - including your Insert Into - to the server via the pass-through query.

/gustav
0
BitsqueezerCommented:
Hi,

if you really have ALL tables on the server you don't need any local query anymore and nothing which is now assembled locally as "on the fly" SELECT is needed anymore.

The most performant solution is to create a stored procedure on the server which do anything from creating the needed data up to inserting the result into the table. The frontend would forward only needed parameters to the stored procedure and this will do the rest.

In the SP, you can then work with BEGIN TRY/CATCH to catch any error and react with error messages for the frontend (my personal method is to use a @strError OUTPUT paramater variable so I can fill that with an error text and format that using a HTML or RTF output) and also it can use BEGIN TRANSACTION / COMMIT TRANSACTION / ROLLBACK TRANSACTION to enclose all steps into a single transaction - that means: If you must do more than one INSERT/UPDATE/DELETE in your procedure, any change they would normally do directly to the table would go into a snapshot of the data at the start point of the procedure and if everything is OK it will only then change the real data. If there is any error in the procedure (like data error) nothing happens to your data, only the frontend gets an error message.

As all steps (like computing any data, assembling dynamic SQL strings and so on) would work on the server the frontend has nothing to do and no data will need to move on the network - all local queries which uses SQL Server tables/views etc. must download the needed data first from the server, compute the result and then (in case of INSERT/UPDATE/DELETE) send them back to the server to write it. If you have many users/records that means heavy work for your network. Doing all the things on the server means: Only the command to execute the SP will be send in a millisecond to the server, then the server do the heavy work without using the network, and the frontend gets back either an error string or a return value or, if you programmed it, a SELECT with a result set only. So, if you don't need a result set, the network has nearly nothing to do, even with thousands of users doing the same thing at the same time. And you can also be sure that SQL Server can work with data a LOT faster than the Access frontend.

Calling the SP can be made with a Pass-Through-Query, if the SP returns records than the result in Access will alway be read-only. If you want to have an updatable recordset you must use VBA and ADO to execute the SP and assign the recordset to the Form's recordset property (can work with ADO and DAO recordsets). You only need to connect with SQLOLEDB driver and use adUseServer as cursor location, otherwise the form is not updatable. That are restrictions of Access only.
You can of course also let the SP only change the data without returning records and then you can use a view which returns the data which is linked like a table in Access and use that as recordsource for your form which is then also updatable.

Cheers,

Christian
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
The syntax differences between T-SQL and Access SQL are minimal.  But, T-SQL doesn't use querydefs, it uses views.  You could either take the query within the append query and spell it out or if it is something you do with some regularity, you could make it a view.  Then you could take the resulting SQL string and save it as a pass-through query.  Use the .execute method to run the pass-through query.  

I would not create a stored procedure unless I needed to incorporate programming logic.  A pass-through query is the equivalent of an Access query.  A stored procedure is like a VBA code loop.  You wouldn't write code if you could use a simple query.

Access attempts to "pass through" all queries when the BE is ODBC.  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.  Sometimes it even runs an update query for every single row that you are trying to update.  When you are updating thousands of rows, that takes a lot of memory and time.  With the pass-through query you won't have the last minute option of changing your mind about applying the updates.  Access will simply hand off the query to the server and wait for a response.

I use SQL Server as the BE for most of my apps.  I use linked tables and normal Access querdefs and bound forms.  I use pass-through queries for the occasional bulk update.  I use stored procedures very rarely.  Generally only for long running batch jobs.
0
pcalabriaAuthor Commented:
Thanks all, I will need to read each of your replies several more times before I understand everything.  I thought it would be best if I provided a couple of examples of my query.

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
0
BitsqueezerCommented:
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
0
Nick67Commented:
@pcalabria
We have discussed this before.  If the point of the exercise is to create the csv, then jamming stuff into a table seems like trying to pound a screw in with a wrench.  But you seem to have your reasons, and I understand your disclosure constraints, so I will desist.  You have received a lot of input above here.  I'll try not to confuse you, or piss any other Expert off :)
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.


Stored Procedures are things created, stored and executed on the SQL Server.  They present possibilities for assembling and marshaling data that Access cannot match.  They can be used for tasks as simple as returning the results of a Select query, or for operations that are completely beyond my experience and imagination at this point.  To use a stored procedure, Access MUST use a pass-through query.  This is the only use I make of pass-throughs; however, a pass-through can be used to pass any and all valid T-SQL straight through to a SQL Server and have the SQL Server execute it.  The scope of what you can do with a pass-through is therefore also beyond my experience and imagination.  SQL Server has features I have never used or explored.

I'm not sure how to take advantage of SQL to optimize speed.  You'll want and need to get your feet wet with what SQL Server can do that Access cannot do efficiently.  Access can call for data and then use VBA to loop through it, manipulate it an massage it as required.  This requires Access to pull the data from the server to do this.  Depending upon the structure of the queries involved and functions used, that may necessitate Access pulling ALL THE DATA from the server.  This is inefficient.  Looping is inefficient.  DBAs call it RBAR (row by agonizing row)

The power of SQL Server lies in set logic.  This is a different way of thinking.  Rather than walking down a large set of data and doing things to each piece, you assemble data that meets pre-defined conditions.  It can be a hard transition.

Note that the query, qryEbayUploadOnlyPhotos2 must be create by my code on the fly.
That is not really the right way to think about it.
The fields you want to SELECT and the WHERE and ORDER BY conditions that apply are dynamically determined.
These are the kind of things that you would pass-through to a cunningly crafted stored procedure.
Stored procedures move beyond what Access guys use in SQL syntax.
They can have logic.
Many of the procedural things we know and love from VBA are available in similar -- and sometimes frustratingly difficult and different -- syntax.
IF, CASE, NZ, FORMAT, C(Int, Str, Dbl, ect) and many more are available.
You can create table variables, and INSERT data into them, run UPDATE on them, and then join them to other tables and other derived tables.
You can run a SELECT Distinct against a derived table, and then self-join it back to its source to do things that are a real trial in Access

But, in general, to take advantage of SQL Server, you want to return to Access only the minimum amount of data needed for Access to do the job.  You want  SQL Server to do the hard work, and not Access.

But this is still verbiage and not any T-SQL :)
Give me a bit.
0
Nick67Commented:
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].[qryFlowLineReadingsByList]    Script Date: 04/24/2015 10:54:23 ******/
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].[qryFlowLineReadingsByList]
      -- 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(tFLR.PieceID, 1) AS Nominal, dbo.ufnCriteriaByPieceType(tFLR.PieceID, 2) AS Minimum
FROM
            (SELECT tblPieces.PieceID, tblPieces.SerialNumber,tblPartDescriptions.PartDescription, tblTypes.TypeName, tblMakes.Manufacturer, tblPieces.PartID
            FROM
                              tblPieces LEFT OUTER JOIN
                              tblPartDescriptions ON tblPieces.PartID = tblPartDescriptions.PartID LEFT OUTER JOIN
                              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].[ufnCriteriaByPieceType]    Script Date: 04/24/2015 11:05:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:            Nick67
-- Create date: 19-Jan-15
-- Description:      returns UT criteria
-- =============================================
CREATE FUNCTION [dbo].[ufnCriteriaByPieceType]
(
      -- 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, isnull(tblCriteria.CriteriaText,'')
FROM (tblPieces INNER JOIN tblPieceCriteria ON tblPieces.PieceID = tblPieceCriteria.PieceID) INNER JOIN tblCriteria ON tblPieceCriteria.CriteriaID = tblCriteria.CriteriaID
Where tblPieceCriteria.PieceID = @PieceID and tblCriteria.TypeNumber = @TypeID
Order by  tblPieceCriteria.PieceCriteriaID

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,10,1)) + ' ' + @CriteriaText
        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
0
pcalabriaAuthor Commented:
Thanks everyone.  I'm still working on this and will update you soon.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.