Francisco H Tapia
my.lists at verizon.net
Tue Jul 29 13:12:00 CDT 2003
Thanks Billy,
that seems to have sped up the sproc from a runtime of 13seconds down to
8-7 seconds nice... tested on a fellow developer's pc, I was running Access
2k out of the box and at work I have Access2k sr1, the other developer has
Access2k sr1 loaded at home and it ran well there too.
so there is some other problem locally at work that breaks this.
-Francisco
http://rcm.netfirms.com
On Tuesday, July 29, 2003 10:26 AM [GMT-8],
Billy Pang <tuxedo_man at hotmail.com> wrote:
: There are a lot of temp tables in the sprocs you are trying to
: running. You can convert them into table variables. They work just
: like regular temporary tables except that it is not written onto disk.
:
: look up data type - table in BOL.
:
: ==cut here ===========================
: SET NOCOUNT ON
: DECLARE @my_product TABLE(PROD_ID INT IDENTITY(1,1) PRIMARY KEY,
: PROD_NAME VARCHAR(50), PROD_DATE SMALLDATETIME DEFAULT(GETDATE()))
:
: INSERT INTO @my_product(PROD_NAME) VALUES('red book');
: INSERT INTO @my_product(PROD_NAME) VALUES('blue book');
: INSERT INTO @my_product(PROD_NAME) VALUES('green book');
: INSERT INTO @my_product(PROD_NAME) VALUES('white book');
:
: UPDATE @my_product SET PROD_NAME = 'BLUE BOOK1' WHERE PROD_ID = 2
: DELETE FROM @my_product where PROD_ID = 3
: SELECT * FROM @my_product
:
: SET NOCOUNT OFF
: =============================
:
:
:: From: "Francisco H Tapia" <my.lists at verizon.net>
:: Reply-To: dba-sqlserver at databaseadvisors.com
:: To: <dba-sqlserver at databaseadvisors.com>
:: Subject: Re: [dba-SQLServer]Sproc runs and then it doesn't
:: Date: Tue, 29 Jul 2003 09:14:44 -0700
::
:: That's right Billy, If unmodiffied Database / sprocs and Unmoddifed
:: Front End Access ADP return the data via Access and QA as expected
:: at home the machine is an AMD2000+ w/ 1gig of ram Win2k SP2 +
:: ss2kdev SP3
::
:: WORK: Dual 800 Intel Xeon w/1gig of ram Win2kServer SP2 +ss2kserver
:: SP3
::
:: well I tried just as you suggested... and no dice :( the >= still
:: doesnt return via access
:: what do you mean Table Variables?
::
:: Thanks
::
:: -Francisco
:: http://rcm.netfirms.com
::
:: On Tuesday, July 29, 2003 8:28 AM [GMT-8],
:: Billy Pang <tuxedo_man at hotmail.com> wrote:
::
::: so both sprocs work perfectly at home?
:::
::: Can you alter both sprocs so that it just does one simple select?
::: if both sprocs work at the office, then alter the sproc again using
::: the real code and see if it still works.
:::
::: also, are you running both sprocs concurrently?
:::
::: also, since you are using ss2k, how about replacing your temp tables
::: with table variables?
:::
:::
:::
:::
:::: From: "Francisco Tapia" <my.lists at verizon.net>
:::: Reply-To: dba-sqlserver at databaseadvisors.com
:::: To: <dba-sqlserver at databaseadvisors.com>
:::: Subject: Re: [dba-SQLServer]Sproc runs and then it doesn't
:::: Date: Tue, 29 Jul 2003 02:05:33 -0700
::::
:::: Billy,
:::: I took the adp, and the database home and updated my home sql
:::: developer copy with the office copy of the server w/ no code
:::: changes and off the cuff copy to my hdd from the office the adp
:::: now displays the record, Sql Server 2000 SP3, but at the office it
:::: still does not and it is the same version of the db, and access
:::: adp.
::::
:::: this is most bazaar.
::::
::::
:::: -Francisco
:::: http://rcm.netfirms.com/
::::
:::: On Tuesday, July 29, 2003 12:36 AM [GMT -8],
:::: Francisco Tapia <my.lists at verizon.net> wrote:
::::
::::: I'm running it as windows authentication at the moment for both QA
::::: and the ADP, doh I did not think to use the profiler, I will check
::::: that next...
:::::
::::: -Francisco
::::: http://rcm.netfirms.com/
:::::
::::: On Monday, July 28, 2003 6:09 PM [GMT -8],
::::: Billy Pang <tuxedo_man at hotmail.com> wrote:
:::::
:::::: Are you tried running both sprocs in QA using the security
:::::: authentication as ADP (eg. same login)?
:::::: also, does SQL Profiler reveal anything?
::::::
::::::
::::::
::::::
::::::
::::::: From: "Francisco H Tapia" <my.lists at verizon.net>
::::::: Reply-To: dba-sqlserver at databaseadvisors.com
::::::: To: <dba-SQLServer at databaseadvisors.com>
::::::: Subject: [dba-SQLServer]Sproc runs and then it doesn't
::::::: Date: Mon, 28 Jul 2003 16:01:33 -0700
:::::::
::::::: I have a stored procedure in my database that runs fine in QA.
::::::: In fact I created 2 versions of this sproc and both of them run
::::::: fine
::::::: in QA. In an ADP
::::::: that I have one sproc runs and the other does not. What would
::::::: be different?
:::::::
::::::: In the where clause I am checking for >= or <.
:::::::
::::::: The code is "I D E N T I C A L" in both sprocs lets call them
::::::: stp_New and stp_Old the new checks for any new entries since
::::::: today 12:00am, and the Old
::::::: checks for any records older than today at 12:00am.
:::::::
::::::: If I run the sproc in Access (ADP) it does not give me any data
::::::: for stp_New but it does give me the correct results for stp_Old.
::::::: If I
::::::: go to QA I can run either sproc and they both execute correctly.
::::::: I
::::::: am at a loss about this
::::::: one... anyone ever run into something similar?
:::::::
::::::: This is my code...
:::::::
:::::::
:::: -----------------------------------------------------------------------
-
:::: -
::::::: ---
::::::: ----------------------------
::::::: ALTER PROCEDURE stp_004A_lstHelpDeskNewCalls
::::::: (@LoginID NVARCHAR(50)) AS
:::::::
::::::: SET NOCOUNT ON
::::::: Declare @SetDateTime as datetime, @entryUser as uniqueidentifier
::::::: SET @entryUser = dbo.udfEntryUSER(@LoginID)
::::::: SET @SetDateTime = CAST (
::::::: CAST (DATEPART(m, getUTCdate()) AS VARCHAR(4)) + '/' +
::::::: CAST (DATEPART(d, getUTCdate()) AS VARCHAR(4))+ '/' +
::::::: CAST (DATEPART(yyyy, getUTCdate()) AS VARCHAR(4)) + '
::::::: 00:00:00' AS DATETIME)
:::::::
::::::: IF EXISTS (select * from tempdb.dbo.sysobjects where id =
::::::: object_id(N'tempdb..#MaxStatus')) BEGIN
::::::: DROP TABLE #MaxStatus
::::::: END
::::::: CREATE TABLE #MaxStatus
::::::: ([HelpDeskID] [UniqueIdentifier] Not Null,
::::::: [HelpStatusID] [int] Not Null,
::::::: [entryTime] [dateTIME] Not Null)
::::::: INSERT INTO #MaxStatus(HelpDeskID, HelpStatusID, entryTIME)
::::::: Select A.HelpDeskID, A.HelpStatusID, A.entryTIME FROM
::::::: dbo.tbl_HelpDeskStatus AS A WITH(NOLOCK)
::::::: LEFT OUTER JOIN
::::::: (SELECT HelpDeskID, Max(entryTIME) as entryTIME From
::::::: dbo.tbl_HelpDeskStatus WITH(NOLOCK) Group By HelpDeskID) AS B
::::::: ON (A.HelpDeskID = B.HelpDeskID AND A.entryTIME = B.entryTIME
::::::: AND HelpStatusID < 2)
:::::::
::::::: IF EXISTS (select * from tempdb.dbo.sysobjects where id =
::::::: object_id(N'tempdb..#HelpDeskCallDetails')) BEGIN
::::::: DROP TABLE #HelpDeskCallDetails
::::::: END
::::::: CREATE TABLE #HelpDeskCallDetails
::::::: ([HelpDeskID] [uniqueidentifier] Not Null,
::::::: [CallNotes] [nvarchar](20) Null,
::::::: [ContactAddrID] [uniqueidentifier] Null,
::::::: [entryTIME] [datetime] Null,
::::::: [entryUSER] [uniqueidentifier] Null)
:::::::
::::::: INSERT INTO #HelpDeskCallDetails (HelpDeskID, CallNotes,
::::::: ContactAddrID, entryTIME, entryUSER)
::::::: Select A.HelpDeskID, LEFT(A.CallNotes, 20), A.ContactAddrID,
::::::: A.entryTIME,
::::::: A.entryUSER FROM dbo.tbl_HelpDeskCallDetails AS A WITH(NOLOCK)
::::::: INNER JOIN
::::::: (SELECT HelpDeskID, Max(entryTIME) as entryTIME From
::::::: dbo.tbl_HelpDeskCallDetails WITH(NOLOCK) Group By HelpDeskID)
::::::: AS B ON (A.HelpDeskID = B.HelpDeskID AND A.entryTIME =
::::::: B.entryTIME )
:::::::
:::::::
:::::::
::::::: Select HD.CaseNumberID, M.SN,Ctc.ContactName,Co.CompanyName,
::::::: Addr.LocationName,ZipList.State,
::::::: HelpDeskCallDetails.CallNotes AS [Call Notes],
::::::: HelpDeskCallDetails.entryTIME
::::::: FROM dbo.tbl_HelpDesk HD WITH(NOLOCK) INNER JOIN
::::::: dbo.tbl_Machine M WITH(NOLOCK) ON HD.MachineID = M.MachineID
:::::::
::::::: LEFT OUTER JOIN
::::::: (SELECT * FROM #MaxStatus WITH(NOLOCK) ) AS MaxStatus
::::::: ON HD.HelpDeskID = MaxStatus.HelpDeskID
:::::::
::::::: INNER JOIN
::::::: (SELECT * FROM #HelpDeskCallDetails WITH(NOLOCK) ) AS
::::::: HelpDeskCallDetails
::::::: ON HD.HelpDeskID = HelpDeskCallDetails.HelpDeskID
:::::::
::::::: LEFT OUTER JOIN dbo.tbl_ContactAddress AS CtcAddr WITH(NOLOCK)
::::::: ON HelpDeskCallDetails.ContactAddrID = CtcAddr.ContactAddrID
::::::: LEFT OUTER JOIN dbo.tbl_Contacts AS Ctc WITH(NOLOCK) ON
::::::: CtcAddr.ContactID
::::::: = Ctc.ContactID
::::::: LEFT OUTER JOIN dbo.tbl_Address As Addr WITH(NOLOCK) ON
::::::: CtcAddr.AddrID = Addr.AddrID
::::::: LEFT OUTER JOIN dbo.tbl_Company AS Co WITH(NOLOCK) ON
::::::: Addr.CompanyID = Co.CompanyID
::::::: LEFT OUTER JOIN dbo.tbl_AddrZipList AS ZipList WITH(NOLOCK) ON
::::::: Addr.ZipID
::::::: = ZipList.ZipID
:::::::
::::::: --THIS LINE BELOW IS WHAT CHANGES from >= to <, both run in QA,
::::::: but only the
::::::: OLD (<) runs in Access
:::::::
::::::: Where HelpDeskCallDetails.entryTIME >= @SetDateTime AND
::::::: HelpDeskCallDetails.entryUSER = @entryUser
:::::::
::::::: ORDER BY HelpDeskCallDetails.entryTIME ASC
:::::::
:::::::
::::::: IF EXISTS (select * from tempdb.dbo.sysobjects where id =
::::::: object_id(N'tempdb..#MaxStatus')) BEGIN
::::::: DROP TABLE #MaxStatus
::::::: END
::::::: IF EXISTS (select * from tempdb.dbo.sysobjects where id =
::::::: object_id(N'tempdb..#HelpDeskCallDetails')) BEGIN
::::::: DROP TABLE #HelpDeskCallDetails
::::::: END
:::::::
::::::: SET NOCOUNT OFF
:::::::
:::::::
::::::: -Francisco
::::::: http://rcm.netfirms.com
::
::
:: _______________________________________________
:: dba-SQLServer mailing list
:: dba-SQLServer at databaseadvisors.com
:: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
:: http://www.databaseadvisors.com
::
:
: _________________________________________________________________
: The new MSN 8: smart spam protection and 2 months FREE*
: http://join.msn.com/?page=features/junkmail
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com