Slow Query With Table Containing Image

Sep 25, 2007

Hi,

I have a table defined as such:
PosterArtId int no 4 10 0 no (n/a) (n/a) NULL
Graphic image no 16 yes (n/a) (n/a) NULL
GraphicFilename varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS

I have a Stored Procedure defined in the database that queries this table (joins with some other tables) that takes about 1 1/2 minutes to return results (running directly in query analyzer). The table itself has 8900 records and the resulting stored procedure returns 33 rows.

I have backed up this database and restored it to another database on a different machine also running SQL Server 2000. When I run the same stored procedure on this 2nd database (note the contents of the database and this table are exactly the same), it runs very quickly - in about 2 seconds.

I'm trying to figure out what is causing the query to run so slow on the original database, which is our production database server (note that none of the other queries seem to be running extra slow on this machine, just this particular one).

I've since been reading up on storing images in the database and I don't think the images are stored "text in row" - I ran the command: €œSELECT OBJECTPROPERTY(OBJECT_ID('tblPosterArt'),'TableTextInRowLimit')€? and got a 0 return value.

My ultimate goal is to figure out:
1) why the timing is so different on the two databases even though the have the same data
2) is there something we can do to speed up the results on our production server

For the first goal, I'm heading down the path that something in the database backup/restore did not recreate the btree storage of the image data in the same manner. Would this be correct? If not, is there some kind of analysis that I can do that will tell me some useful information?

I've run the Stored Procedure for both databases in query analyzer with the "show execution plan", "trace", and "statistics" turned on. In the Execution Plan of the production database I see a significant amount of time in three areas: Nested Loops/Left Semi Join, Clustered Index Scan, and Clustered Index Seek. But being as I'm not a dba (nor do we have one on staff), I'm not sure how to interpret this data. I keep wanting to point to some sort of environment issue since the data is the same between the two machines.

I suppose there is nothing to do about the 2nd goal without knowing why the query on the one machine is taking so long. Any thoughts on how to get more information here?

Thanks,
Beth

View 8 Replies


ADVERTISEMENT

SLOW Performance On Table With Image Fields (SQL 2000)

Nov 15, 2006

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 Related

Save Image Into A Table Using Query Analyzer?

Oct 2, 2003

I have a table with two fields Part_num and Pic in SQL server 2000 Pic is of Image type. Is there a way I can save images for each part_num using Query analyzer?

View 6 Replies View Related

Very Slow Query (select Count(*) From Table)

Feb 15, 2006

Dear MS SQL Experts,I have to get the number of datasets within several tables in my MSSQL2000 SP4 database.Beyond these tables is one table with about 13 million entries.If I perform a "select count(*) from table" it takes about 1-2 min toperform that task.Since I know other databases like MySQL which take less than 1 sec forthe same taskI'm wondering whether I have a bug in my software or whether there areother mechanisms to get the number of datasets for tables or the numberof datasets within the whole database.Can you give me some hints ?Best regards,Daniel Wetzler

View 5 Replies View Related

Slow Performance With A Simple Query In A Small Table?

Jul 9, 2001

In my database/MY SERVER (SQL7/Win2K), I run a simple query with a Table/10000 rows (without cluster index):
SELECT * FROM TABLE
it take over 30s. Why it's slow? How can I check for reason? How to configure my server to improve performance?
Thanks in advance.
TH
----------------------------------
SP_CONFIGURE's RESULT in MY SERVER
----------------------------------

Table 'spt_values'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0

Table 'spt_values'. Scan count 43, logical reads 108, physical reads 0, read-ahead reads 0.
Table 'sysconfigures'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 2.

View 4 Replies View Related

Large Table/slow Query/ Can Performance Be Improved?

Jul 20, 2005

I am having performance issues on a SQL query in Access. My query isaccessing and joining several tables (one very large one). The tables arelinked ODBC. The client submits the query to the server, separated byseveral states. It appears the query is retrieving gigs of data from thetable and processing the joins on the client. Is there away to perform moreof the work on the server there by minimizing the amount of extraneous tabledata moving across the network and improving performance (woefully slowabout 6 hours)?

View 3 Replies View Related

ASP.NET Report Viewer, Slow Rendering, Image Broken

Jan 29, 2008

Hi,

I have been using the report viewer to render my reports on a webpage. All worked fine for a time and now nothing is working correctly.

I have about ~8 report viewer on one page, all in an individual IFRAME. What happen is, sometimes I'm getting an error from IE (Internet Explorer cannot display the webpage) or the report starts loading (I see the title) but where the chart should be I have an X (broken image) icon.

Sometimes when I click on the image and choose "Display Image" the image will display.

Facts


Reports loads correctly on the report manager site (the Reporting Services webpage)
Once those errors starts appearing, the session seems to act funny and I can't even refresh the webpage (F5)
I have the latest report viewer patch installed
I have the SP2 for SQL Server 2005 installed
Running Windows 2000 Server
Running IIS 6
The website was developped using IIS 5, but was tested on IIS 6 for about 3 weeks and we started to have problems today.
The reports are linked with Analysis Services to get the data from cubes.
I'm really out of ideas right now. Maybe I should just restart the IIS server, but the thing is live and I can't do it right now. However, if I need to restart it, will the problem occurs another time? Will it become a solution to restart the server?

I already restarted Reporting Services and nothing has changed.

Thanks

View 2 Replies View Related

A Query Runs Fast In Query Analuser But Slow In APplication

Jul 23, 2005

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 Related

Error Inserting Image Into SQL Server2000 Table From Pocket PC Application Only When Using Stored Procedure In Table Adapter Wiz

Apr 24, 2008

My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.


main.ds.MailsSignature.Clear();

main.ds.MailsSignature.AcceptChanges();


string[] signFiles = Directory.GetFiles(Settings.signDirectory);


foreach (string signFile in signFiles)

{


mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();

mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.

main.ds.MailsSignature.Rows.Add(mailsSignatureRow);

}


mailsSignatureTableAdapter.Update(main.ds.MailsSignature);

But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.


ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )

AS

SET NOCOUNT OFF;

INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);



SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())

For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.

Is there any limitation in CF?

Regards,
Professor Corrie.

View 3 Replies View Related

SQL Server 2012 :: Why Indexes On Table Slow Down DML Operation On Table

Mar 7, 2014

Why the Indexes on table slow down the DML operation on table, what is the exact reason?

View 5 Replies View Related

Very Slow Running Update Query Query

Nov 19, 2004

I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on

the query thats causing teh problem is


--//////////////////////////////////// this is the one thats running


Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
GO
Update Provider_APC_2004_05
set EmAdmsCount12mths =
(Select COUNT(*)-1
from Combined_Admissions
where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
(Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
(Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
Combined_Admissions.AdmMethod like 'Emergency%')-- and
-- CA.NHSorPrivate = 'NHS'))
FROM Provider_APC_2004_05, Combined_Admissions


any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one

Dave

View 6 Replies View Related

Query Analyzer, Slow Query Responses

Sep 25, 2006

Hi there

Running query analyzer against two different server.

the first only need 1-2 secs to return the query result,

the other return 7-8 secs for the query result.

plz advice what could cause this slow performance?



thx



View 1 Replies View Related

VERY Slow SQL Query

Mar 8, 2007

This sounds like a pretty easy one.  I have a SQL 2000 database with 2-3.4GHZ CPUs and 1GB of RAM.  I have one database on it.  I go in Query Analyzer on another machine and run a simple query like 'SELECT * FROM USERS'  which should return 15,000 rows. 
 
IT takes 30 (thirty) seconds to finish this query.  OMG
 
Where do I start to decipher why on Earth this takes more than .01 seconds?
 
Thanks.

View 8 Replies View Related

Slow Query?

Mar 16, 2001

Hi,
I have a query which has suddenly started responding slow.
CAn anyone tell me what could be the possibilities?
I tried update stats(I am on sql 70-though it's done auto but i did it manually again)
I used union all in place of union but had no big effect.any othe thought?
Thanks!

View 4 Replies View Related

A Very Slow Query

Oct 12, 2000

Hi,

I have a query that takes minutes to execute, even through there are about 300,000 records are being processed. I would appreciate any help with optimizing that query.
I have two tables: User and Usage. Table user has two fields: User_Id and Date_Created and a non-clustered index on User_Id. Table usage has two fields also: User_Id and Date_Used and non-clustered index on both fields. The User table is populated when the user registers. The Usage table is populated every time the user opens a document.

Here is what I need to do: get the number of users from the Usage table who opened a document at least once after they have registered during the last 30 days for each day in the time frame, where the time frame varies.
For example, if the time frame is 8/01/00 - 8/31/00, I need to get the following data:

date returns
---- -------
8/01/00 10 (10 users returned to the document between 7/2/00 and 8/1/00)
8/02/00 15 (15 users returned between 7/3/00 and 8/02/00)
.
.
.
8/31/00 20 (20 users returned between 8/1/00 and 8/31/00)

Here is my query:

SELECT [date],
(SELECT count(distinct user_id)
FROM usage u JOIN [user] ON u.[user_id] = [user].[user_id]
WHERE u.[date] BETWEEN usage.[date]-30 AND usage.[date]
AND u.[date]>[user].date_created
GROUP BY usage.[date])returns
FROM usage
WHERE [date] BETWEEN @date1 AND @date2

This query works fine, but too slow. We use MS SQL server 7.0.

Thank you,
Yana

View 2 Replies View Related

Slow Query

Dec 2, 2002

I have a query which responds immediately when run however if I add an order by clause it takes 40 seconds. Below is the query with the order by clause
SELECT distinct Licenseplate, platetypecode.platetypecode, platetypecode.platetypecodeid
FROM Ticket INNER JOIN PlateTypeCode
ON PlateTypeCode.PlateTypeCodeID = Ticket.PlateTypeCodeID
ORDER BY licenseplate

The Ticket table contains approx. 11,000 records. I have created a nonclustered index for the licenseplate field, a 7 char varchar field.
Any suggestions for speeding up the query?

View 6 Replies View Related

Oh So Slow Query...

Jun 15, 2004

The following query is causing some problems because it's taking too long to complete. I looked at the estimated execturion plan and I am unsure why it appears to spend over 50% of its time doing a 'Bookmark Lookup' on on particular column (SRA_SR_ID in the S_EVT_ACT table). There is an index on the column - S_EVT_F14. I'm not sure if the query is using the index properly. What can be done to specifically improve this particular problem? In general, does anyone have some suggestions for optimizing the query as a whole?

Thanks in advance. Clive

SELECT
T1.APPT_REPT_FLG,
T18.X_ALIS_ID,
CONVERT (VARCHAR (10),T1.APPT_START_TM, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_TM, 8),
T1.ASGN_USR_EXCLD_FLG,
T2.NAME,
T19.STAT_CD,
T1.APPT_REPT_TYPE,
T15.NAME,
CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 8),
T1.TODO_CD,
T1.X_DOC_CAT_ID,
CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 8),
T1.TARGET_OU_ID,
T7.ZIPCODE,
T3.ZIPCODE,
T9.EXP_RPT_NUM,
T1.LAST_UPD_BY,
T1.OWNER_PER_ID,
T1.PART_RPR_ID,
T1.RATE_LST_ID,
CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 8),
T1.ACTIVITY_UID,
T4.NAME,
T1.PR_TMSHT_LINE_ID,
T18.LAST_NAME,
T7.ADDR,
T18.SEX_MF,
T1.BILLABLE_FLG,
CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 8),
T1.SRA_SR_ID,
T1.TARGET_PER_ADDR_ID,
T18.X_FST_NAME,
T1.EVT_STAT_CD,
CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 8),
T1.ROW_STATUS,
T1.ACD_CALL_DURATION,
T5.NAME,
T8.FAX_PH_NUM,
T8.X_FST_NAME,
T8.LAST_NAME,
T1.MODIFICATION_NUM,
T1.X_CAMP_ID,
CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 8),
T1.ASSOCIATED_COST,
T13.NAME,
CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
T17.TMSHT_NUM,
T1.PR_SYMPTOM_CD,
T1.OPTY_ID,
CONVERT (VARCHAR (10),T18.BIRTH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T18.BIRTH_DT, 8),
T1.PR_EXP_RPT_ID,
CONVERT (VARCHAR (10),T1.APPT_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_DT, 8),
T8.FST_NAME,
T16.SR_NUM,
T1.SRA_DEFECT_ID,
T1.CREATED_BY,
T8.WORK_PH_NUM,
CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 8),
T1.CALL_ID,
T1.X_CLIENT_ID,
T1.PROJ_ID,
T12.DEFECT_NUM,
T1.CREATOR_LOGIN,
T1.CONFLICT_ID,
T19.OUTCOME_CD,
T1.TEMPLATE_FLG,
T2.PR_ADDR_ID,
T1.PREV_ACT_ID,
T1.X_DOC_NAME,
T1.EXP_RLTD_FLG,
T1.X_BATCH_REF,
T1.PRI_LST_ID,
T1.SRC_ID,
T1.X_POLICY_REF,
CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
T1.EMAIL_FORWARD_FLG,
T11.DMT_NUM,
T1.TMSHT_RLTD_FLG,
T1.ROW_ID,
T10.NAME,
T18.CONSUMER_FLG,
T1.TARGET_PER_ID,
T18.FST_NAME,
T1.PRIV_FLG,
T3.PROVINCE,
T8.X_ALIS_ID,
T8.JOB_TITLE,
T14.NAME,
T1.NAME,
T1.PCT_COMPLETE,
T1.SRA_TYPE_CD,
T1.ALARM_FLAG,
T1.CAL_DISP_FLG,
T1.EVT_PRIORITY_CD,
T1.COST_CURCY_CD,
T2.LOC,
CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 8),
T20.FILE_NAME,
T1.SRA_RESOLUTION_CD,
T6.PRDINT_ID,
T1.OWNER_LOGIN
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_ORG_EXT T2 ON T1.TARGET_OU_ID = T2.ROW_ID
LEFT OUTER JOIN dbo.S_ADDR_ORG T3 ON T2.PR_ADDR_ID = T3.ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T4 ON T1.PRI_LST_ID = T4.ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T5 ON T1.RATE_LST_ID = T5.ROW_ID
LEFT OUTER JOIN dbo.S_ACT_PRDINT T6 ON T1.ROW_ID = T6.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_ADDR_PER T7 ON T1.TARGET_PER_ADDR_ID = T7.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T8 ON T1.TARGET_PER_ID = T8.ROW_ID
LEFT OUTER JOIN dbo.S_EXP_RPT T9 ON T1.PR_EXP_RPT_ID = T9.ROW_ID
LEFT OUTER JOIN dbo.S_OPTY T10 ON T1.OPTY_ID = T10.ROW_ID
LEFT OUTER JOIN dbo.S_PART_RPR T11 ON T1.PART_RPR_ID = T11.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_DEFECT T12 ON T1.SRA_DEFECT_ID = T12.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_INT T13 ON T6.PRDINT_ID = T13.ROW_ID
LEFT OUTER JOIN dbo.S_PROJ T14 ON T1.PROJ_ID = T14.ROW_ID
LEFT OUTER JOIN dbo.S_SRC T15 ON T1.SRC_ID = T15.ROW_ID
LEFT OUTER JOIN dbo.S_SRV_REQ T16 ON T1.SRA_SR_ID = T16.ROW_ID
LEFT OUTER JOIN dbo.S_TMSHT_LINE T17 ON T1.PR_TMSHT_LINE_ID = T17.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T18 ON T1.X_CLIENT_ID = T18.ROW_ID
LEFT OUTER JOIN dbo.S_CAMP_CON T19 ON T1.X_CAMP_ID = T19.SRC_ID AND T1.TARGET_PER_ID = T19.CON_PER_ID
LEFT OUTER JOIN dbo.S_ACTIVITY_ATT T20 ON T1.ROW_ID = T20.PAR_ROW_ID
WHERE
((T1.APPT_REPT_FLG != 'Y' OR T1.APPT_REPT_FLG IS NULL) AND
(T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL)) AND
(T1.SRA_SR_ID = '1-EQLOO')

View 3 Replies View Related

Help Fix Slow Query.

Oct 12, 2006

I have a query that is taking too long to run. It take 14 seconds to return 6800 rows. However, if I move the query out of a stored proc, it takes 1 second. I want to understand this issue and ideally fix the stored proc case.

I've simplified my actual queries for readability.


-- @filter is value to filter against or NULL to return all records.
CREATE PROCEDURE queryPlayerStations(@filter INTEGER)
AS
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))
GO

DECLARE @filter INTEGER
SET @filter = NULL

-- Takes 14 seconds to return 6800 rows. That's unacceptable performance
EXEC dbo.queryPlayerStations @filter


When I run the query directly in Query Analyzer, it runs very fast.


DECLARE @filter INTEGER
SET @filter = NULL

-- Takes ~1 second to return 6800 rows. That's great performance
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))


When I put the parameters in the stored proc it runs fast.


CREATE PROCEDURE queryPlayerStations
AS
DECLARE @filter INTEGER
SET @filter = NULL

SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))
GO

-- Takes ~1 second to return 6800 rows. That's great performance
EXEC dbo.queryPlayerStations


Anyone have any ideas what I can do to improve the stored proc case?

View 2 Replies View Related

Very Very Slow Query

Mar 27, 2008

Hi guys/gals

I have only just signed up, as I have a problem thats confusing me a lot

I have a page (classic asp - sadly) that displays a list of invoices for a certain customer

Each of these customers has fairly complex permissions system, but the page generates a list of invoices and values in 0.01 seconds

However in the while...do loop, for each invoice it checks a database table for "extra charges"..

This is a simple SQL query

"Select Sum(amount) from extra_charges where invoice_number=" & current_invoice_number

However each time it runs this simple select statement it takes 2 seconds - which causes the page to load in 70 seconds instead of 0.01

I am absolutely beyond confused. I have tried it in a stored procedure, and a direct ado query..

The table by the way is empty on my current test system, and one one other system has about 150 records in it. Both take the same time

Any help would be appreciated?

This is a MSSQL 2000 database I've inherited, I am usually a PHP/MYSQL developer - so im a little out of my field of expertise here

View 13 Replies View Related

Slow Like Query

May 26, 2004

i want to have a like search in the following query.

SELECT DISTINCT TOP 200 a.AccountID,
a.AccountNumber,
c.CLI,
con.SurName,
addr.Address1 [Account Address],
addr.Postcode as [Account Postcode],
atp.Name AS Type,
cs.Code AS Status
FROM account_t a
INNER JOIN customer_t cust on a.customerID = cust.CustomerID
INNER JOIN AccountType_T atp on cust.AccountTypeID = atp.AccountTypeID
INNER JOIN CustomerStatus_T cs ON a.CustomerStatusID = cs.CustomerStatusID
INNER JOIN Contacts_T con on cust.MasterContactID = con.ContactID
INNER JOIN Address_T addr ON cust.MasterAddressID = addr.AddressID
LEFT OUTER JOINCLI_T c ON a.AccountID = c.AccountID
WHERE (c.CLI LIKE @CLI + '%')
AND (con.SurName LIKE @Surname + '%')
AND (addr.Address1 LIKE @Address + '%')
AND (REPLACE(addr.Postcode, ' ', '') LIKE @Postcode + '%')
AND c.DateArchived IS NULL

here all fields @CLI, @Surname, @Address, @Postcode are varhcar types...which is making this query very slow...is there any suggestion to improve this query?

thanks in advance
bhavya

View 11 Replies View Related

Slow Query

Mar 18, 2008

When I want to display the total records (#) in a webpage, it is very slow. When I try to remove the total records and show them per 20s, it responds very fast.

Any ideas?

View 2 Replies View Related

Query Is Slow Sometimes

Sep 13, 2006

What might be going on here? The Query is against a single table withsome criteria. The database is active with upto 200 connected usersand at peak times there are 10 or more active sessions. Most of thetime, the query comes back in milliseconds. Occasionally though, it itcan take a whole minute. I've been watching CPU, Memory, Disk. Noneof these appear to be the bottlenecking. (CPU usually below 10% andalways below 50%, pages/sec is 0, and disk % is low and does notspikes during hangs) I also checked to see if hangs were synching withTlog backups or other scheduled jobs, but that is not the case. Thebox has good hardware 4GB RAM and 2 CPU at 3.4 GHz. What could beholding this query up?Thanks for any ideas.Dave

View 1 Replies View Related

Slow Query

Jul 20, 2005

This UNION query is very slow. With only 3,000 records in the Parent tableand 7,000 records in the Child table, it takes about 60 seconds to run andreturns about 2200 records.Any ideas on speeding it up? Thanks.-- PART 1: HAS NO CHILD RECORDSSELECT P.PROJECT_ID, 'No Child Data' AS SUB_TYPEFROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN CON P.PROJECT_ID = C.PROJECT_IDWHERE P.PROJECT_ID IS NULLUNION-- PART 2: HAS CHILD RECORDS, BUT NOT OF TYPE ZSELECT PROJECT_ID, 'Child Data, Not type Z' AS SUB_TYPEFROM PROJECTSWHERE PROJECT_ID NOT IN((SELECT PROJECT_IDFROM PROJECTS_CHILDRENWHERE CHILD_TYPE Like "Z*")ANDPROJECT_ID NOT IN (SELECT P.PROJECT_IDFROM PROJECTS P LEFT JOIN PROJECTS_CHILDREN CON P.PROJECT_ID = C.PROJECT_IDWHERE P.PROJECT_ID IS NULL));

View 4 Replies View Related

Query Too Slow

Jul 20, 2005

Excuse me in advance fo my little English.I've got this stored procedure************************************************** ************************************declare @Azienda as varchar(3), @Utente as varchar(20),@DataDa as datetime, @DataA as datetime,@AreaDa as varchar(3), @AreaA as varchar(3),@LineaDa as varchar(3), @LineaA as varchar(3),@TipoDa as varchar(3), @TipoA as varchar(3),@FamigliaDa as varchar(3), @FamigliaA as varchar(3),@ProdottoDa as varchar(20), @ProdottoA as varchar(20),@AgenteDa as varchar(4), @AgenteA as varchar(4),@NazioneDa as varchar(50), @NazioneA as varchar(50),@ZonaDa as Varchar(3), @ZonaA as Varchar(3),@ProvinciaDa as varchar(2), @ProvinciaA as varchar(2),@ClienteDa as Varchar(12), @ClienteA as Varchar(12),@DestinDa as varchar (5), @DestinA as varchar (5),@TipoDestinDa as varchar(1), @TipoDestinA as varchar(1),@FlagProdNoTarget as varchar(5),@GrAcqDa as varchar(10), @GrAcqA as varchar(10),@TipoCliDa as varchar(3), @TipoCliA as varchar(3),@SettMercDa as varchar(3), @SettMercA as varchar(3)Set @Azienda = '900'Set @Utente = 'Eugenio'Set @DataDa = '2004-01-01'Set @DataA = '2004-01-10'Set @AreaDa = 'UNI'Set @AreaA = 'UNI'Set @LineaDa = ''Set @LineaA = 'ZZZ'Set @TipoDa = ''Set @TipoA = 'ZZZ'Set @FamigliaDa = ''Set @FamigliaA = 'ZZZ'Set @ProdottoDa = ''Set @ProdottoA = 'ZZZZZZZZZZZZZZZZZZZZ'Set @AgenteDa = ''Set @AgenteA = 'ZZZZ'Set @NazioneDa = ''Set @NazioneA = 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZ'Set @ZonaDa = ''Set @ZonaA = 'ZZZ'Set @ProvinciaDa = ''Set @ProvinciaA = 'ZZ'Set @ClienteDa = ''Set @ClienteA = 'ZZZZZZZZZZZZ'Set @DestinDa = ''Set @DestinA = 'ZZZZZ'Set @TipoDestinDa = ''Set @TipoDestinA = 'Z'Set @FlagProdNoTarget = 'Vero'Set @GrAcqDa = ''Set @GrAcqA = 'ZZZZZZZZZZ'Set @TipoCliDa = ''Set @TipoCliA = 'ZZZ'Set @SettMercDa = ''Set @SettMercA = 'ZZZ'Select WSDFR.AreaCommerciale,WSDFR.Agente,WSDFR.NazDestin,WSDFR.ZonaDestin,WSDFR.ProvDestin,WSDFR.Cliente,WSDFR.DescrCliente,WSDFR.GruppoAcq,WSDFR.TipoCli,WSDFR.SettMerc,WSDFR.CDestin,WSDFR.DescrDestin,WSDFR.TipoDestin,WSDFR.EsclStatis,WSDFR.EsclTarget,WSDFR.ValoreNetto,WSDFR.TpDocum,WSDFR.VCambioITL,WSDFR.VCambioEUR,WSDFR.MeseFatt,WSDFR.PosizioneFrom W_St_DocFatt_Righe WSDFRinner join UniP_Prodotti UPP onWSDFR.prodotto=UPP.CodWhere WSDFR.Dtdocum between @DataDa and @DataA andWSDFR.AreaCommerciale between @AreaDa and @AreaA andWSDFR.LineaProdotto between @LineaDa and @LineaA andWSDFR.TipoProdotto between @TipoDa and @TipoA andWSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA andWSDFR.Prodotto between @ProdottoDa and @ProdottoA andWSDFR.Agente between @AgenteDa and @AgenteA************************************************** **************************************************"W_St_DocFatt_Righe" is a view.This query run on my SQL7 server and it takes about 10 seconds.This query exists on another SQL7 server and until last week it took about10 seconds.The configuration of both servers are same. Only the hardware is different.Now, on the second server this query takes about 30 minutes to extract the same details, but anybody has changed any details.If I execute this query without Where, it'll show me the details in 7seconds.This query still takes about same time if Where isWhere WSDFR.Dtdocum between @DataDa and @DataA andWSDFR.AreaCommerciale between @AreaDa and @AreaA andWSDFR.LineaProdotto between @LineaDa and @LineaA and--WSDFR.TipoProdotto between @TipoDa and @TipoA and--WSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA andWSDFR.Prodotto between @ProdottoDa and @ProdottoA andWSDFR.Agente between @AgenteDa and @AgenteAorWhere WSDFR.Dtdocum between @DataDa and @DataA andWSDFR.AreaCommerciale between @AreaDa and @AreaA and--WSDFR.LineaProdotto between @LineaDa and @LineaA and--WSDFR.TipoProdotto between @TipoDa and @TipoA andWSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA andWSDFR.Prodotto between @ProdottoDa and @ProdottoA andWSDFR.Agente between @AgenteDa and @AgenteAorWhere WSDFR.Dtdocum between @DataDa and @DataA andWSDFR.AreaCommerciale between @AreaDa and @AreaA and--WSDFR.LineaProdotto between @LineaDa and @LineaA and--WSDFR.TipoProdotto between @TipoDa and @TipoA and--WSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA and--WSDFR.Prodotto between @ProdottoDa and @ProdottoA andWSDFR.Agente between @AgenteDa and @AgenteAIt is a real puzzle!What happen?Is there someone that had such as problems and have the right solution?Thanks in advance.ByeEugenio

View 11 Replies View Related

Query Slow

Apr 17, 2008

Is there a more efficient way to write this query? It takes 2 minutes and 15 seconds to run against 200,000 records.


UPDATE Tbl_a

SET Flag = NULL

WHERE Flag = 1 And Not Exists(Select Id From Upload

Where Tbl_a.Id = Upload.Id)



thanks,

View 12 Replies View Related

How To Query By Keyword For Image Field.

Jul 23, 2005

If the data type of field is "varchar",we can use "like" to query if it hassome substring.Such as "where custom.valuevariant like '%Verizon%' ", it will query out allrecords that contains string "Verizon".But how to do when data type of field custom.valuevariant is "image"?Thanks

View 2 Replies View Related

Slow Query With Joins And Where

Feb 5, 2008

Hi all.I want to use the following query in a sp to enable paging using ObjectDataSource.The problem (being EXTREMELEY slow) arises when I add these joins and where statements.
SELECT r.RID AS ReqID, r.Name AS ReqName, r.Family AS ReqFamily,t3.Name AS DistName, t4.Name AS RurName,t5.Name AS VilName, n.Name+' '+n.Family AS NazerName ,ROW_NUMBER() over (order by r.Family) AS RowRankFROM Requests rLEFT OUTER JOIN Nazeran n ON r.nazerID = n.ID LEFT OUTER JOIN t1States t1 ON t1.ID = r.StateID LEFT OUTER JOIN t2Provinces t2 ON t1.ID = t2.StateID AND r.ProvID = t2.ID LEFT OUTER JOIN t3Districts t3 ON t2.ID = t3.provID AND t1.ID = t3.stateID AND r.DistID = t3.ID LEFT OUTER JOIN t4RuralDistricts t4 ON t3.ID = t4.distID AND t2.ID = t4.provID AND t1.ID = t4.stateID AND r.RurID = t4.ID LEFT OUTER JOIN t5Villages t5 ON t4.ID = t5.rurID AND t3.ID = t5.distID AND t2.ID = t5.provID AND t1.ID = t5.stateID AND r.VilID = t5.IDWHERE r.stateid=(case when @StateID is null or @StateID='' then r.stateid else @StateID end)  and r.provid=(case when @provID is null or @provID='' then r.provid else @provID end)  and r.rID=(case when @ReqID is null or @ReqID='' then r.rID else @ReqID end)  and isnull(r.nazerID,'')=(case when @nazerID is null or @nazerID='' then isnull(r.nazerID,'') else @nazerID end)  and r.name+' '+r.family like (case when @ReqName is null or @ReqName='' then r.name+' '+r.family else '%'+@ReqName+'%' end)
**there are 1million rows in [Requests] table ,200000 rows in [t5villages], and about total 5000 rows in other tables.As you can see, this is for a GridView showing list of people requesting a loan allowing users to make alternative searches based on Name, Familyname ,ID ,...
would you please help me optimize and make fast this query.Many thanks..

View 13 Replies View Related

Query Process Very Slow

Feb 19, 2008

Hai i use Sql Server 2000  today i got this problem
when i execute "select * from service_db"  some times it is executed successfully but most of times the query on execution continusly
why this happen how to solve this pbm very urgent

View 2 Replies View Related

Sql Query Execution Using Asp.net Became Very Slow

Dec 2, 2003

Hi all,

I have sql query to search for fields in a rather big view. If I execute the
query in sql server enterprise manager, the results will be displayed in
less than 6 seconds. However, if I execute it using asp.net, it will take
very long (more than 2 minutes).

The query is a simple one like "SELECT * FROM myview WHERE name LIKE
'%Microsoft%'". And the code I use to execute it in asp.net is

Dim dsRtn As DataSet
Dim objConnection As OleDbConnection
Try
objConnection = GetOleDbConnection()
objConnection.Open()
Dim objDataAdapter As New OleDbDataAdapter(strSearch, objConnection)
Dim objDataSet As New DataSet()
objDataAdapter.Fill(objDataSet, strTableName)
dsRtn = objDataSet
Catch ex As Exception
dsRtn = Nothing
Finally
If objConnection.State = ConnectionState.Open Then
objConnection.Close()
End If
End Try

Where strSearch is the sql search string.

I don't have any problem using such code for other queries.

Could somebody suggest the cause of the problem and how to solve it? Thanks!

Best regards,
David

View 9 Replies View Related

Execution Of Query Is Very Slow

Jul 1, 2004

Hi,

I am having a query where I am connecting to eight different tables using joins. When I join one table to another the speed of the execution becomes less. Even on my local server it is taking nearly 2 to 3 minutes to execute the query. How can I increase the speed of execution of my query.

Thanks in advance,
Uday

View 1 Replies View Related

Slow JOIN Query

Mar 6, 2002

This queries performance is acceptable (about 1 second) when run like this:

SELECT a.f1,a.f2,b.ff1,b.ff2 FROM table1 a LEFT OUTER JOIN table2 b ON a.id = b.id AND b.ff3 = 'T'
WHERE a.mydate BETWEEN '3/4/2002' AND '3/6/2002' AND b.ff4 = 'somevalue'


It is terrible (60 seconds) when run like this:
SELECT a.f1,a.f2,b.ff1,b.ff2 FROM table1 a LEFT OUTER JOIN table2 b ON a.id = b.id AND b.ff3 = 'T'
WHERE b.mydate BETWEEN '3/4/2002' AND '3/6/2002' AND b.ff4 = 'somevalue'


I need the date range to come from the b.maydate. The field is indexed. If I run another query directy on table2 b without a JOIN and using b.mydate for some daterange it is quite fast. Any idea how to speed this up?

Thanks,Adrian

View 1 Replies View Related

Very Slow Query Analyzer

Aug 12, 2006

Please help me out:
It is textremely slow when I run a query in My SQL Server 2000 Query Analyzer on my laptop. But when I turn off the wireless card on the laptop, the query runs instantly.
Could you please tell me how can I make the server running faster when my computer is connected to the internet?

View 1 Replies View Related

How To Optimize This Slow Query?

Mar 25, 2004

tblElements.ID is a foreign key to tblOwner.eID
@PrevRec = dynamic number of previous records
@owner = owner ID

SELECT TOP 2 tblOwner.eID FROM tblOwner
LEFT JOIN tblElements ON
tblElements.ID = tblOwner.eID
WHERE tblOwner.own_ID = @Owner AND tblElements.ID NOT IN
(SELECT TOP + @PrevRec + tblOwner.eID FROM tblOwner
LEFT JOIN tblElements ON
tblElements.ID = tblOwner.eID WHERE tblOwner.own_ID = @Owner) ORDER BY tblOwner.eID ASC

This query is used to display one record per page on a ASP paging script, I select top 2 because, I want to be able to know if I have another page or record to go to and deside if I need to display the "Next" button.

Also It is suppose to select records only assigned to it's ower.
It works fairly quick when viewing first few hundred records, then it takes a very long time.

I have indexed ID and eID as clustered indexes also tblOwner could have multiple instances eID.

Is there an easier way of writing this out.

View 3 Replies View Related







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