Question Regarding A View That Is Taking Long Time To Process

Sep 1, 2006

Good afternoon everyone, I have written a view that pulls customer demographic  infomration as well as pulling data from multiple scalar-valued functions.  I am using this view to pull and send data from one database to another in the same SQL server.  The problem that I am having is that I am running this import as a scheduled job in windows.  The job is taking almost 24 hours to complete this task.  The total number of records that are being pulled is around 21,000+.  I have tried removing the functions from the view and it only takes the view 20 seconds to pull the demographic information from the same 21,000+ records but when I add the function calls this is where the time to complete goes through the roof.  Has anyone encountered this before if so what would you suggest doing?  Any help would be appreciated.
 Here is the syntax for my view:

 SELECT TOP 100 PERCENT CUS_EMAIL AS Email, CUS_CUSTNUM AS MemberID, CUS_PREFIX AS Prefix, CUS_FNAME AS FirstName,
CUS_LNAME AS LastName, CUS_SUFFIX AS Suffix, CUS_TITLE AS Title, CUS_STATE AS State, CUS_COUNTRY AS Country, CUS_ZIP AS ZipCode,
CUS_SEX AS Gender, CAST(CUS_DEMCODEA AS nvarchar(20)) + ',' + CAST(CUS_DEMCODEB AS nvarchar(20))
+ ',' + CAST(CUS_DEMCODEC AS nvarchar(20)) + ',' + CAST(CUS_DEMCODED AS nvarchar(20)) AS DemoCodes,
dbo.GetSubScribedDateMLA(CUS_CUSTNUM, CUS_EMAIL) AS MLASubscribedDate, dbo.GetSubScribedDateMLP(CUS_CUSTNUM, CUS_EMAIL)
AS MLPSubscribedDate, dbo.GetSubScribedDateLDC(CUS_CUSTNUM, CUS_EMAIL) AS LDCSubscribedDate, dbo.GetMLAExpiration(CUS_CUSTNUM,
CUS_EMAIL) AS MLASubExpireDate, dbo.GetMLPExpiration(CUS_CUSTNUM, CUS_EMAIL) AS MLPSubExpireDate,
dbo.GetLDCExpiration(CUS_CUSTNUM, CUS_EMAIL) AS LDCSubExpireDate, dbo.IsProspect(CUS_CUSTNUM, CUS_EMAIL) AS AGMProspect,
dbo.IsCurrentCustomer(CUS_CUSTNUM, CUS_EMAIL) AS AGMCurrentCustomer, dbo.IsMLAMember(CUS_CUSTNUM, CUS_EMAIL) AS MLAMember,
dbo.IsMLPMember(CUS_CUSTNUM, CUS_EMAIL) AS MLPMember, dbo.IsLDCMember(CUS_CUSTNUM, CUS_EMAIL) AS LDCMember,
dbo.CalculateTotalRevenue(CUS_CUSTNUM, CUS_EMAIL) AS AGMTotalRevenue, dbo.GetPubCodes(CUS_CUSTNUM, CUS_EMAIL)
AS ProductsPurchased, dbo.GetEmailType(CUS_CUSTNUM, CUS_EMAIL, CUS_RENT_EMAIL) AS EmailType, CUS_COMPANY AS Company,
CUS_CITY AS City
FROM dbo.CUS
WHERE (CUS_EMAIL IS NOT NULL) AND (CUS_EMAIL <> '') AND (CUS_EMAIL_VALID = 'Y') AND (CUS_EMAIL LIKE '%@%.%') AND (CUS_RENT_EMAIL = 'Y' OR
CUS_RENT_EMAIL = 'R' OR
CUS_RENT_EMAIL = 'I') AND (CHARINDEX(' ', CUS_EMAIL) = 0) AND (CUS_EMAIL NOT LIKE '@%')
 

Thanks in advance
 
Michael Reyeros

View 9 Replies


ADVERTISEMENT

Taking To Long Process

Jun 1, 2006

To all,



Im migrating data but its taking too long to commit insertion, what sould i do?



merwinp

View 2 Replies View Related

SQL Query Taking Too Long To Process

Jun 19, 2007

dear guys. i have this one problem, where the sql statements really took very long time to be processed. It took more than 1 minute, depending on the total data in the table. I guest this have to do with the 'count' statements. here is the code:

------------------------------------------------------------
$sql = "SELECT company,theID,abbs,A as Active,N as Nonactive,(A+N) as Total
FROM(
select distinct D.nama As company, C.domID As theID, D.abbrew As abbs,
count(distinct case when B.ids is NOT NULL THEN A.dauserid END) As A,
count(distinct case when B.ids is NULL THEN A.dauserid END) As N
FROM
tableuser A LEFT OUTER JOIN tabletranscript B on (A.dauserid=B.dauserid)
INNER JOIN thedommember C ON(C.entitybuktiID=1 AND C.mypriority=1 AND

C.entitybuktiID=A.dauserid)
INNER JOIN mydomain D ON (C.domID=".$getID.")
GROUP BY D.nama, C.domID, D.abbrew
ORDER BY company
)";


Hope any of you can simplify this statements into a query that doesnt take ages to be processed.

Thanks in advance....

View 1 Replies View Related

ExecuteQuery Taking A Long Time

Sep 4, 2007

Hi:

I have a query which returns approximately 50000 records, I am using a linked server to connect to two databases and retrieve data. For some reason it is taking a liitle more than hour to execute the query, but on MS Sql Server query window it comes after few minutes but the query runs for a long time.

How can expediate my query execution process.

Environment details

Database: MS Sql Server 64bit 2005
MS Sql jar file: sqljdbc_1.2.jar
OS: Windows both server and client.

Connect String in java code:

jdbcqlserver://sample_server:1433;databaseName=sample_db;user=admin_user;password=admin_pwd

and use PreparedStatement and ResultSet.

Regards
Arup

View 2 Replies View Related

Dm Query Taking Long Time

May 16, 2007

I'm running a query (see below) on my development server and its taking around 45 seconds. It hosts 18 user databases ranging from 3 MB to 400 MB. The production server, which is very similar but with only 1 25 MB user database, runs the query in less than 1 second. Both servers have been running on VMWare for almost 1 year with no problems. However last week I applied SP 2 to the development server, and yesterday I applied Critical Update KB934458. The production server is still running SQL Server 2005 Standard SP 1. Other than that, both servers are identical and running Windows 2003 Server Standard SP 1. I'm not seeing this discrepancy with other queries running against user databases.



use MyDatabase

GO

select db_name(database_id) as 'Database', o.name as 'Table',

s.index_id, index_type_desc, alloc_unit_type_desc, index_level, i.name as 'Index Name',

avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,

page_count, avg_page_space_used_in_percent, record_count,

ghost_record_count, min_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count,

schema_id, create_date, modify_date from sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED') s

join sys.objects o on s.object_id = o.object_id

join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id

where db_name(database_id) = 'MyDatabase'

order by avg_fragmentation_in_percent desc

--order by avg_fragment_size_in_pages desc

--order by page_count desc

--order by record_count desc

--order by avg_record_size_in_bytes desc

View 4 Replies View Related

Restore Taking Very Long Time

May 31, 2007

i have sql 2000 db of about 120 GB. its taking about 10 -12 hours to restore on the same disk as new database.



server configuration is good.



when i try to restore another db of about 10 GB size, its restoring in about 5 minutes.

View 2 Replies View Related

MSSQLSeverOLAPServieces Is Taking A Long Time To Start

Oct 13, 2003

Sometime is necessary to stop MSSQLSeverOLAPServieces to do a full backup in my OLAP Server disks. After backup had finished and I tried to star MSSQLSeverOLAPServieces but it takes almost 30 minutes to the services starts.
What can it be causing that?

Paulo

View 5 Replies View Related

Stored Proc Taking A Very Long Time

Aug 2, 1999

I have a stored procedure that normally takes about 5 hours to complete:
DELETE tblX WHERE PROC_DT < dateadd(day, -93 , getdate())

tblX has about 55 million records and has an index on PROC_DT.

I have this running as a scheduled task. Over the weekend, the task executed and it is still running 56+ hours later. Does anybody have any ideas as to where I should look for the problem? I am afraid to kill the process because of the rollback time.

View 4 Replies View Related

Update Taking Long Time In 2000 Then SQL 7.0

Mar 7, 2002

Hi,
I have a table with 48 million rows,when i executed following update query it is taking 10 HOURS in SQL SERVER 2000 with SP1.
Where as when i executed same query in SQL SERVER7.0 with same table then it is taking 13 MINUTES. Comming to Machine...SQL 2000 Server has more processors and greater memory than SQL 7.0 m/c.
It looks strange but this is true.Does any one faced such problem..is there any bug in SQL 2000?????

Here is Query::

update cus_pay_jan_dist set univ_regdate = b.dayid
from cus_pay_jan_dist a with (nolock), tm_dayids b with (nolock)
where a.univ_regdate = b.dayidnum and a.univ_regdate like '2001%'


Thanks
Ananth

View 6 Replies View Related

Taking Too Long Time For Insert Statement

Sep 19, 2007

Hi All,

Scenario:

There are two applications running on different server say ServerA and ServerB. Both applications are using same database server SQL Server 2005 say ServerB. Called the application as ApplicationA and ApplicationB with respect to Server names

It means for ServerA the database is remote and for ServerB, database is local.

Both the applications are Java application and using datasource to connect to the database. The driver used are SQL Server 2000 driver (which includes 3 jars). This can be a question that why 2000 driver is used for 2005. The reason is, application on ServerA is getting error while using SQL Server 2005 as Driver not proper.

Problem Area:

When ApplicationB (local to database) is doing some DB operations (which includes select and then batch insert), ApplicationA (remote) is trying to insert a record which is taking too long time (around 40 sec.). This is causing timed out in ApplicationA.

ApplicationA is inserting the data into the same table from where ApplicationB is selecting the data.

Any help????

Cheers
Nitin

View 2 Replies View Related

Update Statment Taking Long Time

Mar 27, 2008



I have an update statment in my SSIS that use to take 10 minutes in SQL 2000 dts and now its take 1 hour 15 minutes in SQL 2005.

this is my sql update statment -
Update WeeklySalesHistory set
weekendingdate =
(SELECT LastTransDateTime from ReplicationControl
where TableName = 'WEEKHST')
where weekendingdate is null

It is using ole db connection. About 36,000 records that it is updating.

I have read ole db can be slow and to use staging table. Does that mean on all updates like this I have to use a staging table and then insert. I didn't use to have to do this in SQL 2000. Has it changed. Are there any other options?


any input greatly appreciated.


View 7 Replies View Related

Transaction Log Backup Taking A Long Time

Mar 30, 2007

Hello,

I'm trying to figure out why my transaction log backup is taking up to an hour to complete. I started off with a full recovery model with a Full database back up every Sunday, differential backups every Tuesday/Thursday and log backups every 5 minutes. I would have thought that the log file backups would execute much quicker because I'm backing them up more often.

Here is my backup statement, I'm hoping I've got a wrong option that you can point out to me:

BACKUP LOG [xxxx] TO [LogFilexxxxBackups] WITH NOINIT , NOUNLOAD , NAME = N'xxxx log backup', SKIP , STATS = 10, NOFORMAT

View 1 Replies View Related

Bulk Insert Taking Long Time To Run

Apr 30, 2008



The process is as follow,


The destination table is truncated and indexes are dropped before loading and after data being inserted we re-create the indexes.

Before this, a view extracts data from more than 22 tables from a staging database and tries to insert this data in the destination table.

it used to take 12-15 mins, but since yesterday loading one particular table never completes. While loading, the database is set to Simple recovery. There are no blocking. It's part of a daily batch thats loads 6 GB of data everyday. But while loading on particular table it's just keep running for hours. I tried rebuilding the indexes and re-starting the SQL Server but of no use.


Any help is much appreciated as this production batch job.


Thanks in advance.

View 4 Replies View Related

SQL 7.0 Restore Taking Long Time Reading Db History

Apr 18, 2002

I have a backup mainentance plan that does a full backup daily at 03:00am and then 2 minute transaction log backups throughout the day to a raided hard drive (It is set to overwrite after 2 weeks), When i go into enterprise manager and select the database to restore it just seems to take too long to read the backup history in. Can this time be reduced as i need to be able to restore the database A.S.A.P but still need a point in time restore to within 2 minutes of going down??

Thanks

View 1 Replies View Related

SSIS Package Validation Taking A Long Time

Apr 19, 2006

I have SSIS Projects taking a long time to open with packages with a large number of data flows. Is there a way to turn off validation of metadata when a package opens? Turn off validation during execution on SSIS Service (after previously validated in dev)? Or be able to control when validation takes place in general?

In my one package (1 of 5) I have 43 data flows (with a single source to target mapping) in 4 sequence containers, and it takes approximately 2-3 seconds per source to target mapping and sequence container to validate which will translate to 1 ½ to 2 ½ minutes to open. When the project with all 100+ tables for the data warehouse goes through validation, I can make coffee in the time it takes to open the project. I have to delete *.suo file (or verify all packages are closed in the designer and save the project file), and when I open the project, I have to jump immediately to SSISÃ Work Offline to set it to not validate the metadata to be able to work in a timely fashion. DelayValidation=TRUE does not help much.

Running in debug mode, has an effect of causing packages that were not open and validated to go through validation though I am not running those packages. Validate once during design and run forever.

Even if I re-open a package that I just closed from designer and had gone through validation, it will go through the validation process again.

It would be great if there could be an on-demand option off the menu bar to allow one to control when validation can take place for a project, or a more granular validation option for a specific data flow or container.







View 7 Replies View Related

Why Is Taking So Long To Open/create/render The Reports For The First Time?

Jan 14, 2006

Hi,
 
Why is taking so long to open/create/render the reports for the first time? Is there any configuration to change this? I don€™t think this behavior is related to Report Execution or cache! I think there is something else going on! Thanks.

View 12 Replies View Related

Table Update Process Is Taking Too Much Time

Mar 9, 2008



hi,


I'm using a OLE DB COMMAND component to perform an update (SQL statement) but the procces takes about 9 hours, so I changed it to a stored procedure but it was the same, I need to update about a a million of rows and the package is very simple.

How can I improve the time, Can I use another component or startegy?

thanks

View 3 Replies View Related

MS Time Series - Quick To Process The Model But Takes Very Long Time To Open Mining Model Viewer

Oct 27, 2007

Hi all,

I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?

View 1 Replies View Related

What Is Taking So Long?

Feb 13, 2008

Hello.

I have a query that takes 1,5second to execute, but only 150ms of CPU. The query is quite simple, just one where statement against a clustered index.

SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 1595 ms.



SELECT column1, column3, column4, ..., column10 FROM table WHERE column2 IN (37, 41, 43, 45, 49, 53, 55) ORDER BY column3 DESC



|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=37 OR [u].[searchparamid]=41 OR [u].[searchparamid]=43 OR [u].[searchparamid]=45 OR [u].[searchparamid]=49 OR [u].[searchparamid]=53 OR [u].[searchparamid]=55 OR [u].[searchparamid]=59) ORDERED FORWARD)





I have tried to rewrite the query to an INNER JOIN instead.


|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([spal].[number]))
|--Index Seek(OBJECT:([MP].[dbo].[__search_parameters_lookup].[IX___search_parameters_lookup] AS [spal]), SEEK:([spal].[hash]=-1726604993) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=[spal].[number]) ORDERED FORWARD)


but the query still takes 1,5 seconds.


It spends 59% (according to execution plan) of sorting. 14% for the index seek of the __search_parameters_lookup table and then 24% of a clustered index seek of the __searchtest table.


How come it only uses that small of CPU but it still takes 1,5 seconds? It seems to be reading from memory as well so it shouldnt be an IO-problem?

The index I have on the table is a clustered index on (column 2).

Any ideas of how I can improve this? I have tried with DTA, also with a non clustered index on column3.

If I remove some columns from the SELECT-list the query will execute alot faster:

SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 32 ms.

Booth the CPU and the elapsed time goes down and now appears to be more normal.

So there seems to be a problem caused by data transfer.
I tried to do a remake and normalize the table and when I do that I get the query execute with a speed of 400ms CPU and 400ms total. And this is still the exact same result, so why does it only spend 400ms of "rendering" or fetching the data when the tables are normalized but 1500ms when its denormalized?

Any ideas?

I am running Microsoft SQL Server 2000 - 8.00.2039

View 6 Replies View Related

What Is Taking So Long?

Jan 28, 2008

Hello.

I have a query that takes 1,5second to execute, but only 150ms of CPU. The query is quite simple, just one where statement against a clustered index.

SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 1595 ms.





Code Snippet

SELECT column1, column3, column4, ..., column10 FROM table WHERE column2 IN (37, 41, 43, 45, 49, 53, 55) ORDER BY column3 DESC





Code Snippet

|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=37 OR [u].[searchparamid]=41 OR [u].[searchparamid]=43 OR [u].[searchparamid]=45 OR [u].[searchparamid]=49 OR [u].[searchparamid]=53 OR [u].[searchparamid]=55 OR [u].[searchparamid]=59) ORDERED FORWARD)




I have tried to rewrite the query to an INNER JOIN instead.





Code Snippet

|--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([spal].[number]))
|--Index Seek(OBJECT:([MP].[dbo].[__search_parameters_lookup].[IX___search_parameters_lookup] AS [spal]), SEEK:([spal].[hash]=-1726604993) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=[spal].[number]) ORDERED FORWARD)

but the query still takes 1,5 seconds.


It spends 59% (according to execution plan) of sorting. 14% for the index seek of the __search_parameters_lookup table and then 24% of a clustered index seek of the __searchtest table.


How come it only uses that small of CPU but it still takes 1,5 seconds? It seems to be reading from memory as well so it shouldnt be an IO-problem?

The index I have on the table is a clustered index on (column 2).

Any ideas of how I can improve this? I have tried with DTA, also with a non clustered index on column3.

If I remove some columns from the SELECT-list the query will execute alot faster:

SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 32 ms.

Booth the CPU and the elapsed time goes down and now appears to be more normal.

So there seems to be a problem caused by data transfer.
I tried to do a remake and normalize the table and when I do that I get the query execute with a speed of 400ms CPU and 400ms total. And this is still the exact same result, so why does it only spend 400ms of "rendering" or fetching the data when the tables are normalized but 1500ms when its denormalized?

Any ideas?

I am running Microsoft SQL Server 2000 - 8.00.2039

View 7 Replies View Related

Restore Taking Very Long

Jun 15, 2007

im rstoring a db the file is 7gig. Its taking more than 10 minuts..
how do i know if the backup file is ok to restore it

=============================
http://www.sqlserverstudy.com

View 4 Replies View Related

ADD CONSTRAINT TAKING LONG TI

Sep 27, 2007

Hi:

I have issued the following ALTER TABLE CHECK ADD CONSTRAINT on a table which has around 100K rows and it is taking long time (it's been more than 30 mins the alter table is running) to add the constraint. Is this normal or should I kill the process.

ALTER TABLE [dbo].[tblAbsHeqAnalyticOutputSimulationPathValues]
WITH CHECK ADD CONSTRAINT [CK_tblAbsHeqAnalyticOutputSimulationPathValues_1]
CHECK ([dbo].[svfConstraintVerifyTableUniqueActiveEntryFacade]('tblAbsHeqAnalyticOutputSimulationPathValues')<=(1) AND [dbo].[svfConstraintVerifyTableUniqueActiveEntryFacade]('tblAbsHeqAnalyticOutputSimulationPathValues')>=(0))



Thanks !

View 8 Replies View Related

SQL Backup Taking Too Long!!!

Nov 17, 2007

Hi All

I am having a serious problem which I need some help with regarding our SQL Server backup.

Basically it has started to take ages (as in 48hrs +), when it should only take about 4 hrs. The database is only 380GB and up until monday our backups have not been completing. When I check the activity monitor I have seen that the 'BACKUP DATABASE' process is set to suspended with a huge wait time and the wait type is ASYNC_IO_COMPLETION.

I am not sure how to solve this, but I am going to have to!

So if anyone has any ideas please help me! If you need any othe info please let me know.

Thanks

Gopher

View 7 Replies View Related

UDF Taking Too Long To Run Workaround ?

Apr 28, 2006

I have written a UDF into which I pass a table name, field name, value of the field, whether alpha characters are valid, whether numerics are valid, and a string of alphanumerics that are valid. I return back a string with all invalid characters removed. Unfortunately when I use this on names and addresses in an 12000 row table, it takes forever to run. Can anyone think of an easy way to do this which isn't so labour intensive. Please see code below.

NB CHAR(32) is space, CHAR(45) is -,CHAR(39) is '

CREATE FUNCTION dbo.UDF_RemoveInvalidCharacters
( @sTableName varchar(50),-- e.g. 'Contact'
@sFieldname varchar(50),-- e.g. 'Lastname'
@sFieldValue varchar(500),-- e.g. 'Jeremi@h O''Grady84'
@sAlphaValid char(1),-- e.g. 'Y'
@sNumericValid char(1),--e.g. 'N'
@sAlphanumericsValid varchar(500))--'CHAR(32):CHAR(45):CHAR(39)'
RETURNS varchar(500)
AS
BEGIN
DECLARE @sReturnValue varchar(500),
@nTableID int,
@nFieldLength int,
@nCurrentPos int,
@sTestChar char(1),
@sValid char(1),
@nAlphanumericPos int,
@sAlphanumericTest varchar(8),
@sTempTestChar varchar(8),
@sAlphasFound char(1),
@sNumericsFound char(1),
@sAlphanumericsFound char(1)

--Get ID of table that the field is on
SELECT @nTableID = [id]
FROM SYSOBJECTS
WHERE [name] = @sTableName

--Get the length of the field
SELECT @nFieldLength = sc.length
FROM SYSOBJECTS so, SYSCOLUMNS sc
WHERE so.id = @nTableID
AND sc.id = @nTableID
AND sc.name = @sFieldName

--Initialise values
SET @sReturnValue = ''
SET @nCurrentPos = 1
SET @sValid = 'N'
SET @sAlphasFound = 'N'
SET @sNumericsFound = 'N'
SET @sAlphanumericsFound = 'N'

--Test each character to ensure it is valid before adding it to the return string, a string consisting solely of alphanumeric characters would be wrong
WHILE @nFieldLength >= @nCurrentPos
BEGIN
SET @sTestChar = substring(@sFieldValue,@nCurrentPos,1)
IF @sAlphaValid = 'Y' --alphas are valid
BEGIN
IF UPPER(@sTestChar) in ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
BEGIN
SET @sValid = 'Y'
SET @sAlphasFound = 'Y'
END

END
IF @sNumericValid = 'Y' AND @sValid <> 'Y'--numerics are valid
BEGIN
IF @sTestChar in ('0','1','2','3','4','5','6','7','8','9')
BEGIN
SET @sValid = 'Y'
SET @sNumericsFound = 'Y'
END
END
SET @nAlphanumericPos = 1
WHILE LEN(@sAlphanumericsValid) > @nAlphanumericPos AND @sValid <> 'Y' --alphanumerics that are valid
BEGIN
IF CHARINDEX(':',SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,LEN(@sAlphanumericsValid))) > 0
BEGIN
SET @sAlphanumericTest = SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,CHARINDEX(':',SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,LEN(@sAlphanumericsValid)))-1)
END ELSE
BEGIN
SET @sAlphanumericTest = SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,(LEN(@sAlphanumericsValid)-@nAlphanumericPos)+1)
END
SET @sTempTestChar = 'CHAR(' + RTRIM(LTRIM(STR(ASCII(@sTestChar)))) + ')'
IF @sTempTestChar = @sAlphanumericTest AND (@sAlphasFound = 'Y' OR @sNumericsFound = 'Y') --alphanumerics are only valid once we have alpha or numerics
BEGIN
SET @sValid = 'Y'
SET @sAlphanumericsFound = 'Y'
END
SET @nAlphanumericPos = @nAlphanumericPos + LEN(@sAlphanumericTest) + 1
END
IF @sValid = 'Y'
BEGIN
SELECT @sReturnValue = @sReturnValue + @sTestChar
END
SET @nCurrentPos = @nCurrentPos + 1
SELECT @sValid = 'N'
END
IF @sAlphanumericsFound = 'Y' AND @sNumericsFound = 'N' AND @sAlphasFound = 'N' --alphanumerics on their own are not valid
BEGIN
SELECT @sReturnValue = ''
END
RETURN @sReturnValue --in the example I would get Jeremih O'Grady

View 1 Replies View Related

Query Taking Too Long

Apr 3, 2007

Below is my query which is taking a long time to execute, DB is SQL Server 2005 through a web Application
I have downloaded the latest MS SQL 2005 driver 1.xxx and still the query takes long to execute

The Description field is a Full_text indexed catalog column
the p.vendornumber is a primary key same with c.ID

Any one have an idea why it is taking this long to run

The Execution Time is: 13640 ms Which I think is very long

SELECT Upper(p.Type) Type,p.Modelname,p.partno,Upper(p.description) description,
Upper(p.classification)classification,p.vendornumber,p.mfg,
p.price,c.CompanyName,c.City,c.State,p.thumbnail
FROM P_all p, Acts c
WHERE p.vendornumber = c.ID
AND CONTAINS(p.Description, '"helmet*"')
Order by p.VendorNumber

Thanks

View 5 Replies View Related

Stored Proc Taking Too Long

May 20, 2008

Dear All

I have a stored proc that is taking way too long to process, over 50 minutes.

What I need to do is go through 180K rows, and if the projId and langid is the same, increment 1 to a value, if not, reset the incrementer

I am doing the following at the moment

DECLARE @Id int, @ProjectId int, @LangCode char(6)
DECLARE @CurProjectId int, @CurLangCode char(6)
DECLARE @incrementer int
DECLARE Order_cursor CURSOR FOR

SELECT Id, langCode, projectid FROM #tempTable

OPEN Order_cursor
SET @incrementer = 1
FETCH NEXT FROM Order_cursor INTO @Id, @LangCode, @ProjectId
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@CurProjectId = @ProjectId)
AND (@CurLangCode = @LangCode)
BEGIN
SET @incrementer = @incrementer + 1
END
ELSE
BEGIN
SET @incrementer = 1
END
UPDATE #tempTable
SET edbOrder = @incrementer
WHERE Id = @id
SET @CurProjectId = @ProjectId
SET @CurLangCode = @LangCode
FETCH NEXT FROM Order_cursor INTO @Id, @LangCode, @ProjectId
END
CLOSE Order_cursor
DEALLOCATE Order_cursor


Is there a better way?

Thanks

Johann

View 2 Replies View Related

CONTAINS And WHERE Clause Combination Taking Too Long

Aug 21, 2007

Hi,

I have a table with 3 columns and 20 million records.
first 2 columns have VARCHAR(4) data type and third column is VARCHAR(5000).
I put 3rd column under FULLTEXT and implement a normal INDEX on 1st column.
Now when i try to search

SELECT

TOP 20

col1,
col3
FROM

tbl
WHERE

col1 = '1234'
AND

CONTAINS(col3,'"market*"')

I am facing following problems
1- It hang for like 1 minute and give 2 records, whereas if i remove col1='1234' from where clause it take less than 1 second.
2- Some time it show criteria is too complex, although i am only requesting a single word in col3.

I am noob in FULL-TEXT but i have done all research in books, microsoft forum and Google and not getting any information.

Please assist.

View 4 Replies View Related

Stored Procedure Taking Too Long

May 2, 2006

I have a stored procedure that is taking too long to complete. I'ved narrowed the problem down to the following code. Is there any reason why this should take long to complete for about 40,000 records?

UPDATE Entries

SET EntryStatus = 1, reason = 'code entered more than once'

WHERE TimeSubmitted > @StartOfPeriod AND TimeSubmitted < @EndOfPeriod

AND TimeSubmitted <> @t and ShortCode = @ShortCode

AND verbatim = @v

View 4 Replies View Related

Forced Failover Taking Too Long ... Am I Missing Something?

Jan 24, 2007

Hi all,

We have to support forced failover under certain scenarios. Whilst using 'ForceFailoverAndAllowDataLoss' work perfectly the database is not available for several minutes. The failover command returns very quickly, but the database enters a recovery/restore state and will not accept connections for up to 2 minutes.

Am I missing an additional step that would speed this up?

Many thanks,

Nick

View 2 Replies View Related

Select Statement In Sql Server 2000 Is Taking Too Long??

Jul 20, 2005

Hi All,I am facing problem in MS SQL Server 2000. It is behaving slow forselect statements. It is even slower than MS ACCESS. For example, if iuse"Select count(*) from tbl;". i get the results after long time ifthere is more than 100k rows. What might be the possible reasons forthis??ThanksHoque

View 1 Replies View Related

It Takes A Long Time To Insert The First Record Each Time When The Program Start

Dec 15, 2006

I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.

The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?

I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.

        cn.Open()
        sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
        scb = New SqlCeCommandBuilder(sda) 
        sda.Update(dataset)
        cn.Close()

I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:

1. Start the PPC Program

2. Load DB into dataset

3. Create a ONE new record in dataset

4. Fill back to DB

When I take this four steps everytime, the filling time is almost 1s or even more!

Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)

 However, when I give up the dataset and using the following code:

            cn.Open()
            Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)

           cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()
            cn.Close()
            StartTime = Environment.TickCount

 I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!

View 1 Replies View Related

Long Running Process

Jan 10, 2001

Hi all,

I have 3 three scheduled job: one runs onece a day, one runs once per hour, and another runs every 17 minutes. It is a NetIQ application. I just scheduled SQL Server maintianace job last night which ran at 2:00Am and 4:00Am. This morning, I came in office and found all my jobs were still running; and they were all blocked by the first 3 jobs. I had to kill all of them. In this afternoon, I kicked off one of my many DTS packages which runs usually about 40 minutes, but it failed. I tried several times but no luck. I suspected one of user tables corrupted or one of stored procedures corrupted. After I recycle the server, and dropped the table and the stored procedure, and recreated them, the package went fine. The store procedure involves many updates and inserts.

The question I have is: is it possible to cause this problem because I killed the unfinished jobs (especially the sql maintanace job)?

NOTE: the sql maintanace job does not include the backup of database and transaction log.

View 1 Replies View Related

Throttling A Long-running Process

Apr 13, 2007

I have a SQL procedure that can take several minutes to complete.  I allow users to initiate the process through a web site and view a progress bar.  When the process is running, though, the site slows to a crawl or times out completely.  That long running process seems to block all other queries on the database.  Is there a way to give this process a low priority or somehow throttle its resource use so that the other web processes can get a chance to run in a timely manner?
Thanks for any advice.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved