Hello ,
I'm a newbie,I programmed a store procedure,and when it runs in query analyzer,
I get results very fast,but when I add it in a job,then this job run very very slow,
(I add records and delete records in the SP), WHY?
TIA!
James
Performance issue.
I have a very complex Stored Procedure called by a Job that is Scheduled to run every night.
It's execution takes sometimes 1 or 2 hours and sometimes 7 hours or more.
So, if it is running for more than 4 hours I stop the Job and I run the procedure from a Query Window and it never takes more than 2 hours.
Can anyone help me identify the problem ? I want to run from the Job and not to worry about it.
Some more information:
- It is SQL 2000 Enterprise with SP4 in a Cluster (It happens the same way in any node).
- The SQL Server and SQL Agent services run using a Domain Account that have full Administrative access.
- When I connect to a Query Window I also use a Windows Account.
- There is no locks or process bloking or being blocked while the job is running.
- Using the Task Manager the processor activity is ok, no more than 30 % in any processor.
I am using SSIS packages for data transfer, When i run the package on virtual server it takes more time as when run on a PC. After analysing i found that Package when run on Virtual server takes time in startup around (50 sec) or so.Could anyone help me with a little bit of detail description as to why it runs slow.
View 10 Replies View RelatedWhen I launch Outlook, it takes forever for the program to finally open. With any inbound email, it stops processing whatever is underway at the time....and frequently there is a 2-3 second lag between keyboard input and what appears on the screen. SQLserver is usually consuming upwards of 1-gb of memory....help. Mike
View 1 Replies View RelatedSome users complain that there computers run slow around noon. later it is fine. Anybody can tell me what is the problem? We have entivirus software installed on. Also i am runing back up database on the server. Many thanks.
View 6 Replies View RelatedWhen i execute a stored procedure it generally takes about half a second to run but sometimes it takes 20 to 30 seconds. I am the only one using the server so I know it is not due to other traffic. I have looked at Profiler and nothing looks out of the ordinary. Another observation is that the slow ones are always near eachother. I will have about 10 fast executions and then 3 slow ones and then back to fast ones. Has anyone seen anything like this before?
View 5 Replies View RelatedWe are having issues w/ a stored proc call in our application. When I run the proc through a query window in Mgmt Studio, it comes back in .3 seconds. However, when the application runs the proc (w/ the same parameters), it takes 30 seconds for the proc to complete. When I run a trace with the proc call through Mgmt Studio, it says 4000 read, but through the app, it says 4 million reads. What is happening?? (app uses Hibernate 3.2/ Java 1.5/ JDBC)
Thanks.
I have SQL Server and OLAP Server running on an NT Server box, and a pc running NT Workstation which has OLAP Manager installed on it. I can register the OLAP Server just fine in OLAP Manager, but it takes about 30sec to a minute for the pc to actually connect to the server.
We've also installed SQL Server and OLAP Server on a Win 2000 machine, and the OLAP connection is faster.
Could this be related to a network routing issue, OLAP or SQL server install issue?
Hi,
the title of this topic about summarises it. I have a dev-machine with SQL Server 2005 Standard edition on it. My host runs SQL Server 2000. What are my options?
Is there some sort of backward compatibility? Can i export as 2000?
I have no idea and i can't find anything about this particular problem.
Thanks
Hi,
I am using SQL2K for a intranet web application database. And I have several database in it.
After a long periode of time (about 6 month) the the application is slowing down when accessing the data.
I use full log for the database.
I also set an autobackup for the database for every 15 minute, but I think this is not what cause the slow down.
The database seem to slow down more and more every day.
I keep only the last 3 days transaction data and every day I delete transaction data older then 4 day, so the number of records in the transaction table is just about 3000 to 5000 records only.
Please help me. Thanks.
Hi,
We have a server that s too slow. The tables have about between 1 million and 3 million records each. We have about 30 tables for production. The other tables are mostly look up tables (just for personnel and categories and so on, only static tables)
1/ do u guys think that s too much data, I mean about 3 millions records in a table. Because the manager said, may be if u archive some records and delete them from the tables, the system might become quick and performant?
2/ When we check the activity monitor in management in entreprise manager, we find a process Id 55 that has the value 1 in the Blocking column, and other processes having that process ID 55 in the Blocked by column.
Does the fact that the blocking column for a process equals 1 mean that the process is having a problem, and how do we know what causes the problem for sure?
3/When we check Performance monitor, pls note that we have RAID5 with 5 disks, we see that Avg. Disk Queue Length counter ranging between 20 and 70 and especially if we run a query for example, although simple select query in one of the tables, the Avg. Disk Queue Length counter goes to the roof to 100 % in performance monitor.
I don t think we have a pb with the CPU, it looks good, not going to the roof.
For your info, we have the web server on that same SQL server machine as well, but the web server s working well, only sql server and the client application that accesses it is slow, the web pages are pretty fast.
Am I following the good path to diagnose what s wrong. It s my first time doing this, and I am trying to learn.
I appreciate your guidance.
I have two tables:T1 : Key as bigint, Data as char(20) - size: 61M recordsT2 : Key as bigint, Data as char(20) - size: 5M recordsT2 is the smaller, with 5 million records.They both have clustered indexes on Key.I want to do:update T1 set Data = T2.Datafrom T2where T2.Key = T1.KeyThe goal is to match Key values, and only update the data field of T1if they match. SQL server seems to optimize this query fairly well,doing an inner merge join on the Key fields, however, it then does aHash match to get the data fields and this is taking FOREVER. Ittakes something like 40 mins to do the above query, where it seems tome, the data could be updated much more efficiently. I would expectto see just a merge and update, like I would see in the followingquery:update T1 set Data = [someconstantdata]from T2where T2.Key = T1.Key and T2.Data = [someconstantdata]The above works VERY quickly, and if I were to perform the above query5 mil times(assuming that my data is completely unique in T2 and Iwould need to) it would finish very quickly, much sooner than theprevious query. Why won't SQL server just match these up while it ismerging the data and update in one step? Can I make it do this? If Iextracted the data in sorted order into a flat file, I could write aprogram in ten minutes to merge the two tables, and update in onestep, and it would fly through this, but I imagine that SQL server iscapable of doing it, and I am just missing it.Any advice would be GREATLY appreciated!
View 3 Replies View RelatedHello there,
I'm using a SQL Server 2000 with a dozen of databases. The databases are rather small (all sizes together sum up to 10 GB).
The entire server gets extremelly slow from time to time (lasting a few days when it happens and suddently coming back to normality). A profiler trace doesn't show anything strange (besides a lot of SQL Agent entries).
I pretty much tried to isolate every single application that makes use of the databases in that server and see if it was the cause of the problem, but I couldn't find any correlation.
I know the computer where this server runs is quite fragmented. Is there anyway I could get to know if this is the cause of my performance issues?
I don't know if this happens, but once the server simply went down for some 3 hours, and I wasn't able to bring it up in anyway. It eventually started working again by itself. The only thing I did in the meantime was to run DBCC CHECKDB a few times, always getting the response "No error found on the database".
Any hint on that?
Hi everybody !!! I have a problem with database SQL server 2000, and I need anyone help me.
I have PC machine with hardware configuration following:
+ RAM : 512M
+ CPU : 2.4 GHz (intel Pentium 4)
+ System : Microsoft Windows Server 2003 Enterprice Edition , Service Pack 1
(this is call Machine1 )
and other Server machine with hardware configuration the following :
+ RAM : 2G
+ CPU : 3.6 GHz( intel Xeon)
+System : Microsoft Windows Server 2003 Enterprice Edition , Service Pack 2
(use RAID 5)
(this is call Machine2 )
.And SQL Server 2000 is installed in these machine.
I created one table and one script to insert several records into this table. and script for inserting:
declare @count int , @max int
set @count =1
set @max = 5000
WHILE (@count < @max or @count = @max)
BEGIN
insert into TBAT_STOCKEOD(TRDT,SEQNO,COMPID,TRANSTYPE) values('20071219',cast(@count as varchar(5)),'13215','1')
set @count = @count +1
END
I execute this script on Machine1 and Machine2 :
+ Machine1 : spent 3 seconds.
+ Machine2 : spent 30 seconds. I don't understand why Machine2 is slower than Machine1 ( while configuration of Machine2 is better than Machine1's ). I don't know what happen.
I checked resource usages in perfmon when SQL server execute script and result following :
Machine1:
Proccessor used 100%
whereas,Machine2 used physical Disk 100%
Is there the way to configurate SQL server to improve speed writting of hard disk?
I am able to run a query which runs FAst in QA but slow in theapplication.It takes about 16 m in QA but 1000 ms on theApplication.What I wanted to know is why would the query take a longtime in the application when it runs fast on SQL server?How should we try debugging it?Ajay
View 2 Replies View RelatedHi All,I have a table that currently contains approx. 8 million records.I'm running a SELECT query against this table that in somecircumstances is either very quick (ie results returned in QueryAnalyzer almost instantaneously), or very slow (ie 30 to 40 seconds toreturn results), and I'm trying to work out how I improve performance.Essentially the query I'm running is nothing more complex than:SELECT TOP 1 * FROM Table1 WHERE tier=n ORDER BY member_id[tier] is a smallint column with a non-clustered, non-unique index onit. [member_id] is a numeric column with a clustered, unique index onit.When I supply a [tier] value of 1, it returns results instantaneously.I have no idea if this is meaningful, but the tier = 1 records wereloaded first into the table, and comprise approximately 5 millionrecords.When I supply a [tier] value of 2, the results take 30 to 40 seconds.tier =2 records were loaded second, and comprise approximately 3million records.I've tried running an execution plan, and while I'm no expert, itappears to me that the index on tier isn't being used, even if I use:tier = CAST(2 as SMALLINT)I'm wondering if anyone can give me ANY advice on how to get anybetter performance out of this SELECT statement?Also, out of curiosity, can a disk defragment have a positive impacton SELECT query performance?Any help very much appreciated!Much warmth,Murray
View 4 Replies View RelatedI have had a problem with Enterprise Manager connecting to SQL Server. At first this problem was experienced with one particular network user... whichever PC he logged onto, Enterprise Manager took ages to connect to SQL Server and every operation was painfully slow. Creating a new Windows NT logon fixed the problem.
I now have this same problem but only on my PC. It doesn't matter which Windows NT logon I use, using Enterprise Manager is painfully slow. I've tried creating a new Windows NT profile, checking the hard drive for errors, defragmenting the disk, reinstalling Enterprise Manager etc but nothing works.
What is strange is that connections from VB applications on my PC are fast. It is only Enterprise Manager that is slow.
I am using the latest service pack for SQL Server.
I thought it could be a problem with the Enterprise Manager registry values but don't want to start messing with them!
Has anyone else experienced this problem? Any advice would be great as I can't find any help from microsoft other than installing the latest service pack for SQL Server.
Thanks
I have a problem with bad perfomance with my import of data from a SQL Server 2000 database. I use an OLEDB datasource in my SSIS package to connect to the sql server 2000 database. My 2005 server runs 64bits but i dont think this is an issue. With this configuration the import is VERY slow, we are talking about 40+ minutes to get 3.5 million rows with about 20 columns. When i create a test DTS package on the SQL Server 2000 server itself and run it, its blazingly fast. Has anyone run into something similar?
View 2 Replies View RelatedHi there,
I was wondering if someone can point out the error or the thing I shouldn't be doing in a stored procedure on SQL Server 2005. I want to switch from SQL Server 2000 to SQL Server 2005 which all seems to work just fine, but one stored procedure is causing me headache.
I could pin the problem down to this query:
DECLARE @Package_ID bigint
DECLARE @Email varchar(80)
DECLARE @Customer_ID bigint
DECLARE @Payment_Type tinyint
DECLARE @Payment_Status tinyint
DECLARE @Booking_Type tinyint
SELECT @Package_ID = NULL
SELECT @Email = NULL
SELECT @Customer_ID = NULL
SELECT @Payment_Type = NULL
SELECT @Payment_Status = NULL
SELECT @Booking_Type = NULL
CREATE TABLE #TempTable(
PACKAGE_ID bigint,
PRIMARY KEY (PACKAGE_ID))
INSERT INTO
#TempTable
SELECT
PACKAGE.PACKAGE_ID
FROM
PACKAGE (nolock) LEFT JOIN BOOKING ON PACKAGE.PACKAGE_ID = BOOKING.PACKAGE_ID
LEFT JOIN CUSTOMER (nolock) ON PACKAGE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN ADDRESS_LINK (nolock) ON ADDRESS_LINK.SOURCE_TYPE = 1 AND ADDRESS_LINK.SOURCE_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN ADDRESS (nolock) ON ADDRESS_LINK.ADDRESS_ID = ADDRESS.ADDRESS_ID
WHERE
PACKAGE.PACKAGE_ID = ISNULL(@Package_ID,PACKAGE.PACKAGE_ID)
AND PACKAGE.CUSTOMER_ID = ISNULL(@Customer_ID,PACKAGE.CUSTOMER_ID)
AND PACKAGE.PAYMENT_TYPE = ISNULL(@Payment_Type,PACKAGE.PAYMENT_TYPE)
AND PACKAGE.PAYMENT_STATUS = ISNULL(@Payment_Status,PACKAGE.PAYMENT_STATUS)
AND BOOKING.BOOKING_TYPE = ISNULL(@Booking_Type,BOOKING.BOOKING_TYPE)
-- If this line below is included the request will take about 90 seconds whereas it takes 1 second if it is outcommented
--AND ADDRESS.EMAIl LIKE '%' + ISNULL(@Email, ADDRESS.EMAIL) + '%'
GROUP BY
PACKAGE.PACKAGE_ID
DROP TABLE #TempTable
The request is performing quite well on the SQL Server 2000 but on the SQL Server 2005 it takes much longer. I already installed the SP2 x64, I'm running the SQL Server 2005 on a x64 environment.
As I stated in the comment in the query it takes 90 seconds to finish with the line included, but if I exclude the line it takes 1 second.
I think there must be something wrong with the join's or something else which has maybe changed in SQL Server 2005. All the tables joined have a primary key.
Maybe you folks can spot the error / mistake / wrong type of doing things easily.
I would appreciate any help you can offer me to solve this problem.
On the web I saw that there is a Cumulative Update 4 for the SP2 which fixes the following:
942659 (http://support.microsoft.com/kb/942659/)
FIX: The query performance is slower when you run the query in SQL Server 2005 than when you run the query in SQL Server 2000
Anyhow I think the problem is something else, I haven't tried out the cumulative update yet, as I think it is something different, more general why this query takes ages to process.
Thanks again for any help
Best regards,
Pascal
An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.
On SQL Server 200 instances the job ran in minutes in the old 2000 package.
Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?
Any inputs will be much appreciated.
Thanks,
MShah
I am using two almost idential laptops, one with XP and one with Vista, the only differences is that the XP laptop has 1G of RAM and running Office XP and the Vista has 2G RAM and is running Office 2007.
I have a MS Access database that has linked tables to a SQL Server 2000 database. The performance of the Access database on Vista is 5-10 times slower on the Vista machine. Just flipping through records or opening forms can take 5 - 15 seconds on the Vista machine while the XP machine takes 1 sec or less.
What gives? I looked at the CPU performance and the network performance while the Access database was busy flipping through records, the network traffic was < 2% and the CPU would spike to 40% on one of the CPUs (dual core) but would remain under 5% most of the time.
I also previously had Office XP installed on the Vista machine and it had the same performance issue so bought and install Office 2007 on the Vista machine and it did not solve the problem.
It seems that Vista is doing something that is slowing down Access with linked tables. Is this a issue between Vista and using an ODBC connection to SQL Server?
Thanks in advance for any help on this
I hav the following problem. I have written an stored procedure in sql server 2000 as the following
CREATE PROCEDURE dbo.pa_rellena
@pFechaInicio datetime
AS
declare @pFechaFin datetime
declare @auxcod_cen char(10)
declare @importeEfectivo decimal(17,2)
declare @importeTarjetas1 decimal(17,2)
declare @importeTarjetas2 decimal(17,2)
declare @importeVales decimal(17,2)
declare @importeTalones decimal(17,2)
declare @importeGastos decimal(17,2)
select @pFechaFin=@pFechaInicio+1
--Borramos las tablas temporales si las hemos creado con anterioridad y no se han borrado
if object_id('tmpCentros') is not null
drop table tmpCentros
if object_id('tmpCentros2') is not null
drop table tmpCentros2
if object_id('tmpMaxCajas') is not null
drop table tmpMaxCajas
if object_id('tmpCajasCentro') is not null
drop table tmpCajasCentro
if object_id('tmpVales') is not null
drop table tmpVales
if object_id('tmpDiarioEfectivo') is not null
drop table tmpDiarioEfectivo
if object_id('tmpDiarioTalones') is not null
drop table tmpDiarioTalones
if object_id('tmpDiarioTarjetas') is not null
drop table tmpDiarioTarjetas
if object_id('tmpDiarioSegundaForma') is not null
drop table tmpDiarioSegundaForma
if object_id('tmpDiarioGastosTarjetas') is not null
drop table tmpDiarioGastosTarjetas
if object_id('temp1') is not null
drop table temp1
--Seleccionamos todos los centros de Salvador Bachiller
select * into tmpCentros2
from centros
where centros.tienda=1
order by cod_cen
--Seleccionamos el maximo de cajas por cada centro
select cod_cen, max(cod_caja) as cajas into tmpMaxCajas
from cierrecaja
where fecha>=@pFechaInicio and fecha<@pFechaFin
group by cod_cen
order by cod_cen
--Mezclamos los centros con el maximo de cajas
select c.cod_cen, c.Centro, c.Direccion, c.localidad, c.provincia, c.cpostal, c.telefono, m.cajas, operaciones, cajas_tot, tienda, franquicia into tmpCentros
from tmpCentros2 as c left outer join tmpMaxCajas as m on c.cod_cen=m.cod_cen
--Cajas por centro
select distinct cod_cen as cod_cen, cod_caja as cod_caja into tmpCajasCentro
from cierrecaja
where fecha>=@pFechaInicio and fecha<@pFechaFin
--Los vales de cada centro
select cod_cen,sum(importe) as imp1 into tmpVales
from vales where
fecha>=@pFechaInicio and fecha<@pFechaFin
group by cod_cen
--Efectivo de cada centro
select cod_cen,'01' as vendedor,'EFECTIVO' as descripcion, (sum(diario.TotEuro)-Sum(Diario.Imppa2)) as importe1,0 as exp1, (sum(Diario.TotEuro)-sum(Diario.imppa2)) as importe2 into tmpDiarioEfectivo
from diario
where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='01'
group by cod_cen
--Talones por centro
select centros.cod_cen,'02' as vendedor,'TALONES' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro) as importe2 into tmpDiarioTalones
from centros inner join diario on centros.cod_cen=diario.cod_cen
where fecha>=@pFechaInicio and fecha<@pFechaFin and diario.cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='02'
group by centros.cod_cen
--Tarjetas por centro
select cod_cen,'03' as vendedor,'TARJETAS' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro*(FPago.Descuento/100)) as importe2, sum(Diario.TotEuro) - sum(Diario.TotEuro*(FPago.Descuento/100)) as importe3 into tmpDiarioTarjetas
from FPago left join Diario on fpago.Cod_pago=Diario.cod_pago
where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0
group by cod_cen
--Segunda Froma de Pago
select cod_cen,'03' as vendedor,'TARJETAS' as descripcion,sum(diario.imppa2) as importe1 into tmpDiarioSegundaForma
from fpago left join Diario on Fpago.cod_pago=diario.cod_pa1
where fPago.cod_pago<>'99' and fecha>=@pfechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0
group by cod_cen
--Comisiones tarjetas de pago
select cod_cen,'10' as vendedor, 'GASTOS (-)' as descripcion, sum(Diario.imppa2*(fPago.Descuento/100)) as importe2 into tmpDiarioGastosTarjetas
from Fpago left join Diario on FPago.cod_pago= Diario.cod_pa1
where fPago.cod_pago<>'99' and fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0
group by cod_cen
/*
--Venta neta por centro
declare cursortemporal cursor for select cod_cen from TmpCentros2
open cursortemporal
delete detallecaja_aux
fetch next from cursortemporal into @auxcod_cen
while @@fetch_status=0
Begin
select @importeVales=imp1 from tmpVales where cod_cen=@auxcod_Cen
select @importeEfectivo=importe2 from tmpDiarioEfectivo where cod_cen=@auxcod_Cen
select @importeTalones=importe2 from tmpDiarioTalones where cod_cen=@auxcod_cen
select @importeTarjetas1=importe3 from tmpDiarioTarjetas where cod_cen=@auxcod_cen
select @importeTarjetas2=importe1 from tmpDiarioSegundaForma where cod_cen=@auxcod_cen
select @importeGastos=importe2 from tmpDiarioGastosTarjetas where cod_cen=@auxcod_cen
select @importeVales=isnull(@importeVales,0)
select @importeEfectivo=isnull(@importeEfectivo,0)
select @importeTalones=isnull(@importeTalones,0)
select @importeTarjetas1=isnull(@importeTarjetas1,0)
select @importeTarjetas2=isnull(@importeTarjetas2,0)
select @importeGastos=isnull(@importeGastos,0)
print @auxcod_cen
print @importeVales
print @importeEfectivo
print @importeTalones
print @importeTarjetas1
print @importeTarjetas2
print @importeGastos
insert into detallecaja_aux (cod_cen,importe1)
values(@auxcod_cen, @importeVales+@importeEfectivo+@ImporteTalones+@ImporteTarjetas1+@importeTarjetas2-@importeGastos)
fetch next from cursortemporal into @auxcod_cen
select @importeVales=0
select @importeEfectivo=0
select @importeTalones=0
select @importeTarjetas1=0
select @importeTarjetas2=0
select @importeGastos=0
end
close cursortemporal
*/
select * from detallecaja_aux
GO
When I try to run it from visual basic it slow down the sql server.
What can I do?
Hi there everybody,
I am writing a sales managing software using C# in .NET 2005.
The program was used to work properly with sql server 2000.
I decided to write a "FILE Version" of sofware using sql server express 2005 , and detached db from 2000 , attaching it to 2005 using AttachDBFileName clause in connection string.
But the problem is when program executes something like this:
EXEC [AddOrder] ...
two rows inserted instead of one.and when I use server explorer of VS2005 to launch SP, it works fine.
I should mention again that same code works correctly when I change connection string and force it to use sqlserver 2000.
Any ideas?
Kind Regards: Ali
I have a rather complex sp that runs for 4 minutes in SQL2000. It computes some messy oil and gas revenue/cost transactions. It involves lots of calls to functions that return single values of all types and also returns recordsets. There are all kinds of joins with temp and memory tables, etc. Just a mess, but it works. However, in SQL2005, it runs and apparently hangs, as it never ends.
I have run the Upgrade Advisor and otherwise, have not found any information that tells me that there are issues with functions or SQL-specific functions, tables, etc. that might cause this. Does anyone on this forum have some pointers on where I might look for assistance on this matter? Surely someone knows something about things working differently in 2005.
Thanks,
Jim
I have a stored proceedure (which I will tag on at the end for those interested) which is taking at least 15 minutes to run when executed, but completes in 1 minute when the tsql statement is run in Query Analyser. Why is this?
I suspect that it may be connected to table indexing, but why then is this bypassed when QA is used?
Any advice appreciated.
Derek
************************************************** ***********************
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_ValidateAIGL') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.sp_ValidateAIGL
GO
CREATE PROCEDURE dbo.sp_ValidateAIGL
@IGLPeriodIDInt,
@IGLProgramIDInt
AS
/* ************************************************** ************************
Name:sp_ValidateIGL
Author:CL
Date:19-Jan-2001
Notes:
************************************************** ************************ */
--SET NOCOUNT ON
DECLARE@TaxYearChar(5),
@FrequencyChar(1),
@PeriodNo Int,
@ProgramLogIDInt
SELECT@TaxYear = TaxYear,
@PeriodNo = PeriodNo,
@Frequency = Frequency
FROMtbl_IGLPeriods
WHEREIGLPeriodID = @IGLPeriodID
SELECT @ProgramLogID = (SELECT ProgramLogID FROM tbl_SYSProgramLogs WHERE IGLProgramID = @IGLProgramID AND IGLPeriodID = @IGLPeriodID)
CREATE TABLE #IGLErrors
(
KeyFieldChar(24),
ErrorIDInt,
DescriptionVarChar(255)
)
-- *** Global Non Program Specific Data Errors ***
-- CHECK - that there are records in the DEB_IGL_PAYROLL_OUTPUT file.....none and the routine failed...
IF NOT EXISTS(SELECT * FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID)
INSERT INTO #IGLErrors SELECT NULL, 100, 'No records were processed by the IGL run!'
SELECT * FROM #IGLErrors
-- CHECK - search for any records where the employee's EXPENSE_CODE is NULL
INSERT INTO #IGLErrors
SELECT DISTINCT
NULLIF(EmpNo, ''),
2,
'Employee "' + COALESCE(NULLIF(RTRIM(EmpNo), ''),
'<Missing Employee>') + '" (Organisation Unit - ' + COALESCE(RTRIM(OrgUnitCode),
'<No Organisation Unit>') + ') does not have a EXPENSE_CODE Code.'
FROM tbl_OUT_Payroll
WHERE NULLIF(ExpenseCode, '') IS NULL
ANDIGLProgramID = @IGLProgramID
SELECT * FROM #IGLErrors
-- CHECK - check that the BALANCE of DEBITs match the balance of CREDITs
IF (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 1 AND IGLProgramID = @IGLProgramID) <> (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 0 AND IGLProgramID = @IGLProgramID)
INSERT INTO #IGLErrors SELECT NULL, 3, 'The total cash value for DEBIT elements does not match the total cash for CREDIT elements.'
SELECT * FROM #IGLErrors
-- *** Program 1 and 2 errors ***
IF @IGLProgramID IN (1, 2)
BEGIN
-- CHECK - search for any records where the employee's COST_CENTRE is NULL
INSERT INTO #IGLErrors
SELECT DISTINCT
NULLIF(EmpNo, ''),
1,
'Employee "' + NULLIF(RTRIM(EmpNo), '') + '" (Organisation Unit = ' + RTRIM(OrgUnitCode) + ') does not have a COST_CENTRE Code.'
FROM tbl_OUT_Payroll
WHERE NULLIF(CostCenter, '') IS NULL
ANDIGLProgramID = @IGLProgramID
SELECT * FROM #IGLErrors
-- Check for EMPLOYEEs that were not transfered to the PAYROLL output (usually caused by missing ORG_UNITs or not picked up in
-- the DEB_VIEW_APPOINTEE view...)
INSERT INTO #IGLErrors
SELECT DISTINCT
EMP_NO,
11,
'Employee "' + RTRIM(EMP_NO) + '" was excluded from the summary. Check their Organisation Unit codes!'
FROM PSELive.dbo.COSTING_OUTPUT
WHERENOT EMP_NO IN (SELECT DISTINCT EmpNo FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID)
ANDPERIOD_NO = @PeriodNo
ANDTAX_YEAR = @TaxYear
SELECT * FROM #IGLErrors
-- Check that there are no ELEMENTS in the COSTING_OUTPUT table that don't exist in the tbl_IGLElements table
INSERT INTO #IGLErrors
SELECT DISTINCT
ELEMENT,
12,
'Element "' + RTRIM(ELEMENT) + '" does not exist in the IGL Interface Elements table!'
FROM PSELive.dbo.COSTING_OUTPUT
WHERE ELEMENT NOT IN
(
SELECT DISTINCT Element
FROM tbl_IGLElements
)
ANDPERIOD_NO = @PeriodNo
SELECT * FROM #IGLErrors
END
-- *** Add a error to indicate the number of errors ***
IF EXISTS (SELECT * FROM #IGLErrors)
INSERT INTO #IGLErrors
SELECT 0,
0,
'Warning, there are ' + CAST(Count(*) AS VarChar(5)) + ' recorded errors!'
FROM#IGLErrors
-- Transfer the records to the ErrorsLog table ready for the user to view...
DELETE FROM tbl_SYSErrorsLog
INSERT INTO tbl_SYSErrorsLog (IGLProgramID, OutputLogID, KeyField, ErrorID, Description)
SELECT@ProgramLogID,
@IGLPeriodID,
KeyField,
ErrorID,
Description
FROM #IGLErrors
ORDER BY ErrorID
DROP TABLE #IGLErrors
SELECT *
FROM tbl_SYSErrorsLog
ORDER BY ErrorID
--SET NOCOUNT OFF
GO
GRANT EXECUTE ON dbo.sp_ValidateAIGL TO Public
GO
Hello.I test some query on sql server 2000 (sp2 on OS windows 2000) and iwant to know why a simple query like this :select * from Table Where Column like '%value'is more slow on 2000 than on sql 7.And this case arrive only if the % character is in the begin.If you test this :select * from Table Where Column like 'v%alue'then it's more fast on 2000.I look the execution plan, there is a difference but this differenceis the same in all the case i test (for the two query i write in thismessage for example).I don't understand why and so, if someone have an explanation, andperhaps a solution ...Excuse my poor english language.And thanks for time people spend to answer me.
View 2 Replies View RelatedI have the following statement that takes quite a long time. Longestof any of my SQL statment updates.UPDATE F_REGISTRATION_STD_SESSIONSET PREVIOUS_YEAR_SESSION_ID = (SELECT s.previous_year_session_idFROM F_REGISTRATION_STD_SESSION R,DS_V4_TARGET.dbo.D_H_Session_By_SessQtr SWHEREr.STUDENT_ID = F_REGISTRATION_STD_SESSION.STUDENT_IDand s.previous_year_SESSION_ID = r.SESSION_IDand s.session_id = F_REGISTRATION_STD_SESSION.SESSION_ID)STUDENT_ID varchar(20) and SESSION_ID char(10) and are indexedWhat I want to accomplish:I want to know if there was a student registration from the prior yearof a registration.Example, if there is a registration for Fall 2004, was there also aregistration for the same student Fall 2003?Maybe there is a better way to approach this?TIARob
View 10 Replies View RelatedI posted this in one of the VB forums but I'm starting to think itmight be more appropriate to have it here, since it really seems to bea SQL server (MSDE/Express 2005) problem:Hey, all, I have a problem with queries against a SQL server instancethat I just found and is causing me fits. I hope someone can pointmein the right direction, please. TIA.Basically, I got a Vista OS machine to test my VB6 app on it as someof my clients will be switching over in the coming months. I went toa local Circuit City during early business hours in the middle of theweek and I installed my application on each of 5 PC's on the newVistaOS (Tuesday, when it was released). I had read that MSDE 2000, whichI normally use as my DB is not supported on Vista so I had downloadedand was using SQL Express 2005. Each system had at least a 1.9 GHzdual core processor and 1 GB of RAM. One process in my program findsrecords in one table that do not match records in another table andthen reports those un-matched entries. On my development machine(laptop with 1 GB of RAM, XP Pro SP2, MSDE 2000 (current SP), 2 GHzCentrino (IIRC)) the process takes less than 30 seconds consistently.On each of those 5 systems at Circuit City the process took 5 minutes(on each of 3 HP machines, a1700n, a1720n, a1730n, and 11 minutes oneach of two Gateway systems (the model numbers of which I forget atthe moment). Each of these computers should be much faster than mylaptop, and some had twice the RAM, and all had SATA or SATA IIdrivesinstead of my piddly 5400 laptop drive, I would have thought they'dall be faster but were abysmally slow.So, seeing a huge difference in the time, and to try to keep thisshort and sweet, I fired up another computer I have, running XP SP2,on 512 MB RAM, AMD Athlon 2300+. First I loaded MSDE 2000 and myapplication and ran the process. < 30 seconds on each of multipleruns. Second, I unloaded MSDE 2000 and installed SQL Express 2005andmoved the DB to it (sp_attach_db) which caused some upgrading(messages reported in OSQL about update/upgrade). When it was done Irebooted, to be sure, and the ran the program and the process again.On the same data, on the same computer, the process took 7-9 minutesconsistently on each of several runs. This makes this part of theapplication unusable, and even the simple stuff like grabbing asinglerecord from the DB (maybe 5 columns of no more than 500 bytes total)is noticeably slower on the SQL Express 2005 than on MSDE 2000.So, the problem seems to be with my interaction with the DB. I amusing ADO 2.8 in VB 6 (SP 6). I use DSN-less connections with aconnection string like: Driver={SQL Server};server=(local)caredata;database=caredata;Uid=sa; Pwd=<password>I use the RecordsSet Object to open the data similar to this:oRS.OpenstrSQL$, oCN, adOpenKeysetafter the oCN object has had the connection string set and the objectis opened.Considering that the same computer, against the same data, with thesame program, takes about 14 times (or more) longer to run, then ithas to be either that SQL Express 2005 is slow OR that my program isinteracting with it in an incorrect manner.Can someone point me in the right direction, please?Thank you.--HCSo, the problem isn't Vista
View 11 Replies View RelatedHi,
We are having SQL2000 Advance Server.
4 processor with hypherthreading, Memory 4 GB and it is a high transactional OLTP server. We are also running Transaction Replication on that server.
We recently bought Double Take and implemented on the server with File Difference with block check sum option for Disaster Recovery Purpose.
The Queue and the Log file folder is on local drive where the system doesn't use that folder except double take.
We are replicating from Source to Taget thru the WAN (DS3).We are replicating approx. 200 Gig of Data but just the difference.
Now the CPU usage is normal,Memory utilization is normal, but the network is a major problem as the Applications connecting to the Server timesout and the applications running very slow.
We have set just like the Tech support recommended.
I would appreciate, if someone give us some recommendations to run the double take without any problem.
Thanks in advance.
Anu
Hi,
Recently our database has been migrated from SQL 2000 to SQL 2005 on a new server(machine) with windows 2003(previously windows 2000). If the database is retained on the same machine but with a named instance of 2005, the application(websphere 5.1) is behaving normal whereas if i configure the aplication to the new server it is running slow for some of the queries but not all.
This change will have to be implemented in production very soon. Any advise will be of great benefit
Thanks
Raam
I have an Access2000 ADP that I want to run under Access2007. The problem I have is that some forms take up to 45 seconds to open in Access2007! These are not complicated forms--just simple navigable reference forms like setting up transaction types etc. that are based on basic select statements like:
SELECT * FROM ArReceivableType
Where ArReceivableType is a reference table (less than 10 columns, all int or nvarchar(100) max) containing about 15 or 20 rows. They open instantly in Access2000.
I put a trace on to see what is happening on the SQL Server, and I noticed heaps of nasty code like this that generates tens of thousands of reads:
select object_name(sotblfk.id), user_name(sotblfk.uid), object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences srfk, sysobjects sofk, sysobjects sotblfk, sysobjects sotblrk where srfk.constid = sofk.id and srfk.fkeyid = sotblfk.id and srfk.rkeyid = sotblrk.id and user_name(sofk.uid) = N'dbo' and object_name(sofk.id) = N'FK_FaAssetTransactionWork_ArReceivableType_ArReceivableTypeId'
It looks like Access2007 is reading all of the constraints for the underlying table, including all foreign keys. My SQL database contains 1400+ tables all with properly constructed foreign keys and other constraints.
Any suggestion on how to NOT have Access2007 do this? Right now, Access2000 works great for this enterprise app, but I really like the new Access2007 features (and I don't want to still be developing Access2000 apps in 2010).
HiWe have a SQL server 2000 SP4 on a windows 2003 2x3Ghz XEON 4 GB ram.We have a table looking like this with currently 6 rows. Total data is aprox10 kb i all row all together.CREATE TABLE [dbo].[BIOMETRICPROFILE] ([BIOMETRICPROFILEID] [bigint] IDENTITY (1, 1) NOT NULL ,[FINGERPRINTTEMPLATE1] [image] NOT NULL ,[FINGERPRINTTEMPLATE2] [image] NOT NULL ,[FINGERPRINTTEMPLATE3] [image] NOT NULL ,[FINGERPRINTTEMPLATE4] [image] NOT NULL ,[FINGERPRINTTEMPLATE5] [image] NOT NULL ,[FINGERPRINTTEMPLATE6] [image] NOT NULL ,[TYPE] [nvarchar] (50) COLLATE Danish_Norwegian_CI_AS NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOselect * from BIOMETRICPROFILE takes ~4 seconds (!) to execute thourgh Queryanalyzer. Alle other tables has no performance problems.We have a SQL 2005 express instalation on the same server. If we restore abackup from the sql 2000 database the query takes aprox ~ 15 ms.What isgoing on here?Has SQL 2000 problems with image fields? or how can we find the problem?RegardsAnders
View 2 Replies View Relatedi was using sql 2000, the database contains 500+ tables, 3000+ sp.
i moved to sql 2005 and found problem on generating script (right click database -> tasks -> generate scripts).
i need to generate the table relations.... it is very very slow compared to sql 2000 which is done in about 30 seconds to few minutes.
i already tried many ways including set options to false which in my thought could speed up a lot...but still very slow.
average generate script time with sql 2005 (sp 2): 70-90 minutes.
average generate script time with sql 2000 (sp 4): 2-3 minutes.
can anyone tell why ? thx in advance.