Why Is A Simple WHERE Clause So Slow?

Jan 12, 2007

Dear Experts,

I have a fairly simple query in which adding a where clause slows
things down by at least a factor of 100. The following is the slow
version of the query

-------------------------
SELECT * FROM
( Select x.event_date From x FULL OUTER JOIN y
ON x.event_date = y.event_date
) innerQ
WHERE ( innerQ.event_date >= {ts '1980-01-01 00:00:00'} )
------------------------

Removing the where clause makes the query run quickly. This seems
extremely strange because it seems like SQL Server should simply be
able to take the results of innerQ and discard anything with a date
that doesn't match. If I instead split the query into two pieces where
I create a temp table and put innerQ into that and then do the select *
WHERE (...) from the temp table things work fine.

Any thoughts on what SQL Server might be doing to make things slow and
how I can fix it?

Thanks,
-Emin

View 9 Replies


ADVERTISEMENT

Slow Where Clause

Apr 15, 2008

Hi,


hoping some one can explain the following

I have a large data set with a few million records in it,
in query analyser if i run the following query it will return 260 results in 19 seconds

the Tel_ID is an int the InvoiceNo is an nvarchar
SELECT * FROM Call_Data WHERE Tel_ID=18668 AND InvoiceNo='519657'

if i run this query then i get the same result but in less than a second

SELECT * FROM Call_Data WHERE Tel_ID=18668 AND InvoiceNo=519657

???

View 9 Replies View Related

Index / Join / Where Clause Very Slow

Jul 15, 2007

Hello,

first of all, some facts of the case:



Table Master Table Dimension
ID Code Price ID Name
1 A44333 5000 1 "Scanner"
2 D442 3000 2 "Notebook"
3 D6644 4000 3 "Banana"


I join both tables on ID and search one time for ID and another time for Name. Looks like

(a)
SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id
WHERE master.id=1
AND Code like 'A44'
(b)
SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id
WHERE Name = 'Scanner'
AND Code like 'A44'


Why does query (b) take longer than query (a)? Dimension has 12 Rows and
Master has about 24M Rows.

For index I did
Create Index IX_Master_ID on Master(ID)
Create Index IX_Master_Code on Master(Code)
Create Index IX_Dimension_ID on Dimension(ID)
Create Index IX_Dimension_Name on Dimension(Name)

I noticed, that when i leave the Code like 'A44' clause, query (a) and (b) do take same time. I'm really confused. Can someone please help me out?

Thank you

Silas

View 9 Replies View Related

Simple Insert Query VERY Slow !

Mar 7, 2005

Hello,

I don't know what to do anymore ;o(

I've got 2 servers, with sql server 2000 sp3 and ms windows 2003 server.
I've written a very simple stored procedure to insert 20,000 rows into a very simple table TEST (id int, msg varchar_50)

On the first server (P-IV 2 GHz), it takes 700 ms / 1000 insertions
and on the second (2x Xeon 2,6 GHz), it takes 13 s / 1000 insertions...

(insertion is : INSERT INTO TEST (id, msg) VALUES (@id, 'dummy text'))

...



SQL Server was installed exactly in the same way...

what could I do see where the problem is ? With profiler, I see no difference while logging all events....

please help or give ideas

View 1 Replies View Related

Slow Report From Simple Query

Apr 2, 2007

Hi all, I've been building a set of traffic based reports on our website and I've run into a strange problem.

The reports are pretty basic, and up till now I've been really impressed with RS overall.

Recently I've added a StartDate and EndDate and since then the performance has gone from ~10 secs to ~10 minutes.

I've taken a really simple query from my reports. Running this query in Management Studio on the same data returns in less than a second. When its run from a test report with nothing else in it it takes ~1 minute. Even stranger when I run the same query with the same values for parameters inside of RS in the data view it takes less than 1 second. ARG!


SELECT COUNT(DISTINCT SessionID) as Occurences
FROM WebAppSummary
JOIN WebAppLocalizations
ON WebAppSummary.ClientIp = WebAppLocalizations.ClientIp
where FirstTime BETWEEN @StartDate AND @EndDate


The last line that was just added is this part:
where FirstTime BETWEEN @StartDate AND @EndDate

So whats going on here? Is this a really poorly performing query that management studio is optimizing but RS isnt? Is RS messing up the databind and getting a bunch of DSs instead of just one?

View 1 Replies View Related

Simple Where Clause

Nov 21, 2005

This where clause basically works:

WHERE
code = 'creq' AND
lsstv NOT BETWEEN 1 AND 80 OR
code = 'nreq' AND
lsstv NOT BETWEEN 1 AND 90

but what I'm really trying to do in words, in the first instance above is:

where the code = treq and lsstv = 0 or lsstv > 80...but the "code" column should only = creq. Obviously the 2nd "Or" in the previous sentence will include other values from the "code" column.

View 1 Replies View Related

Simple UDF Add Where Clause To IF

Jul 10, 2006

Hello,
I'm trying to add a where clause to the return value. Right now, the code counts the number of times a certain relationship type is used. Once the count is done it returns the value, also if the count = 0, then the return value will be populated = 1. So to take this a step further, how would I add another where clause where sometimes I want the count to stay at = 0. For example, I'm trying to add a clause where for certain org_ogt_code='supplier' the return value will always be = 0. I tried adding a where clause to the IF statement, but i keep getting error code 207. Its saids it an invalid column name, but I have the table join. Any help would be greatly appreciated.
Thanks
Bryan



CREATE FUNCTION [dbo].[ngcoa_get_multi_golf_count] (
@org_cst_key_ext uniqueidentifier,
@todays_date av_date)

RETURNS int AS
BEGIN
declare @ret_value int
select @ret_value = 0

select @ret_value = count(*)
FROM
co_customer_x_customer (nolock)
join co_customer (nolock) on cxc_cst_key_2= cst_key
join co_organization on cst_key=org_cst_key
WHERE cxc_cst_key_1= @org_cst_key_ext
and cst_type='Organization'
and org_ogt_code !='supplier'
and (cxc_end_date is null or cxc_end_date>@todays_date)
and cxc_rlt_code = 'Multi-Golf Parent'
and cxc_delete_flag=0

if @ret_value = 0
select @ret_value = 1

return @ret_value
END

View 5 Replies View Related

SQL Server 2012 :: OVER Clause With Insert Very Slow

Sep 29, 2015

I am using an aggregate with the OVER clause.Running the script is fast less than 1 second but when I say insert into a temp table the execution plan is very different at it take 8 seconds.I have attached the execution plans. Also the Statistics IO, Time messages. I am using SQL Server 2014 with backward compatibility to 2008 R2.

if (select OBJECT_ID('tempdb..#MM')) is not null drop table #MM
CREATE TABLE #MM ([MyTableID] [int], [ParticipantID] [int], [ConferenceID] [nvarchar](50), [Points] [money], [DateCreated] [datetime], [StartPoints] [money], [EndPoints] [money], [LowPoints] [money], [HighPoints] [money])
insert into #MM ([MyTableID], [ParticipantID], [ConferenceID], [Points], [DateCreated], [StartPoints], [EndPoints], [LowPoints], [HighPoints])
selectmm.MyTableID, mm.ParticipantID, mm.ConferenceID, mm.Points, mm.DateCreated,

[code]....

View 2 Replies View Related

Problem With Simple Where Clause

Jul 20, 2005

Please Help me. I have a Stored Proc as follows:USE feeschedGOCREATE PROCEDURE [dbo].[sp_UpdateAveragedMedicare]AS-- copy records with alpha in pos 1 that's not JSELECT SUBSTRING([cpt code],2,LEN([cpt code])),amount,inscodeINTO t_AveragedMedicareFROM AveragedMedicareWHERE NOT ISNUMERIC(LEFT([cpt code],1))AND LEFT([cpt code],1) <> 'J'I'm getting the following error message:Server: Msg 156, Level 15, State 1, Procedure sp_UpdateAveragedMedicare, Line 10Incorrect syntax near the keyword 'AND'.Can anyone help me out?Thanks!Tony

View 1 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

SQL Server Express Excruciatingly Slow With Simple Quries

Mar 10, 2008



I am running simple queries against test SQL Express installation and they take a very long time to return data. I have two SQL Express instances installed on colleagues' machines to which I connect for my testing and both exhibit the same problem. The setup is Windows XP SP 2 with 2 GB RAM and 3.6 GHz CPU. I am querying a table with around 7000 records and my query is simply SELECT TOP 1000 * FROM MyTable. It takes over 10 seconds to return the recordset!



I have done the research and found posts, which talk about AUTO_CLOSE option, indexes, query execution plan, etc. I have done everything those posts recommend, but performance is still terrible. All the instances have SQL 2005 SP2 applied.



I also found that the query runs fast locally on each SQL Express instance, the problem seem to happen when I am trying to pull the data over the network.



I am really not sure what else to look for.



Thank you,



Michael

View 1 Replies View Related

Help With Simple Query Using Group By And Where Clause

Jun 13, 2006

have a table with sale_id, date, sales_person_id

i need to find out the sales_person_id's who did 1 sales every month
from jan 2003 and another query who did a sales every quarter.

How many sales person have atleast one sale every month (excluding prints) for either 2003, 2004, or 2005?

How many sales person had atleast 25 sales each year 2003-2005



View 1 Replies View Related

Transact SQL :: How To Create UNION Clause With Two Queries That BOTH Have WHERE Clause

Nov 4, 2015

I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?

I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....

I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.

Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?

View 13 Replies View Related

Simple Simple Linking Tables & Perform Calculation

Mar 22, 2007

found it

View 3 Replies View Related

Simple Question (Hope Simple Answer Too)

May 26, 2004

Hey,

I have MS SQL database.
I have procedure:



code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId

begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------


I got error when using variable with tables names.
How to do this?

Ps. Number is send to this function and it must drop table with name Reg_[That number]

View 1 Replies View Related

GROUP By Clause Or DISTINCT Clause

Jul 23, 2005

Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View 3 Replies View Related

Filtering Results In The Where Clause Vs A Having Clause

Oct 25, 2007

I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.

When on SQL Server 2000 the following statement ran without issue:

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN

(


SELECT TrackID

FROM dbo.Track_ID

GROUP BY TrackID

HAVING MAX(LegNum) = 1 AND


TrackID + 'x1' IN


(


SELECT

dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions


ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)

)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.

I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?


UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN
(



SELECT TrackID

FROM dbo.Track_ID

WHERE TrackID + 'x1' IN


(


SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions


ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)
GROUP BY TrackID

HAVING MAX(LegNum) = 1

)

View 3 Replies View Related

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause

May 14, 2008

2 examples:

1) Rows ordered using textual id rather than numeric id


Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id






Result set is ordered as: 1, 11, 2
I expect: 1,2,11


if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

2) SQL server reject query below with next message

Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.




Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id




Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

It reproducible on

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


and


Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In both cases database collation is SQL_Latin1_General_CP1251_CS_AS

If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.

Could someone clarify - is it bug or expected behaviour?

View 12 Replies View Related

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error


ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause


I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View 1 Replies View Related

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error


ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause


I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View 1 Replies View Related

Having Clause Without GROUP BY Clause?

Nov 20, 2004

Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.

View 3 Replies View Related

Top Clause With GROUP BY Clause

Apr 3, 2008

How Can I use Top Clause with GROUP BY clause?

Here is my simple problem.

I have two tables

Categories
Products

I want to know Top 5 Products in CategoryID 1,2,3,4,5

Resultset should contain 25 Rows ( 5 top products from each category )

I hope someone will help me soon.
Its urngent


thanks in advance

regards
Waqas

View 10 Replies View Related

Diff In On Clause And Where Clause?????

Apr 4, 2007

hi..
i have basic question like

what is differance between conditions put in ON clause and in WHERE clause in JOINS????

see conditions that shown in brown color

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)


select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

another thing...

if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????

View 6 Replies View Related

SQL Inner Join Clause And The Where Clause

Jan 21, 2008

Hi everyone,
I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?

Thanks

View 6 Replies View Related

Simple Join Not So Simple

Feb 21, 2007

I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.

result set

colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e

So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.

Any help is appreciated.

View 7 Replies View Related

Simple Join Not So Simple

Aug 19, 2006

Hi,

I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)

I have:


Master table Hotel

table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)


table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')


foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)

however not all hotels have thumbnail image

hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media


the problem is that if there is no entry in Media or mediaXref, I don't get any results

i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned


any clue?

thanks

View 5 Replies View Related

Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?

Jul 20, 2005

Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave

View 5 Replies View Related

Why So Slow?

Nov 13, 2003

I am writing a data access web page, but I find that the excution speed is too slow.
My data base is just a data table which have five columns: id, code, quantity, price and Date. The data base has about 45000 rows. When I use OSQL or Query function, speed is just fine.

Here is the main code which I think cause the speed slow:

string conn = ConfigurationSettings.AppSettings["connectionstring"];
SqlDataAdapter adapter_2 = new SqlDataAdapter("select * from table",conn);
DataSet ds = new DataSet();
adapter_2.Fill (ds,"table");
DataTable YahooOrders = ds.Tables["YahooOrders"];
DataRow[] product = new DataRow[20000];

.......

foreach (string s in split) // actually the split here has only one string in it
{
product = table.Select ("code like '"+s+"%' and Date >='"+minDate+"' and Date <='"+table.Select("Date = Max(Date)")[0][1].ToString()+"'");
foreach(DataRow myRow in product)
{
int count = Convert.ToInt32(myRow[2]);
itemQuantity = count + itemQuantity;
revenue = Convert.ToDouble(myRow[3]) * count + revenue;

// get product code, ignore repeated code
int myIndex=code.BinarySearch( myRow[1] );
if ( myIndex < 0 )
code.Add(myRow[1]);
}
orderQuantity = product.Length + orderQuantity;
}
The first foreach actually excutes just one time, so it won' t cause any speed problem.
The second foreach' s job is to sum each column of specified rows which is product here.
So, any ideas about this?
Thanks!

View 8 Replies View Related

Too Slow

Mar 8, 2001

All,

Actually from the application the developers are using count(column) to know the no. of rows resulted by a statement which joins many tables but its taking lot of time.

Is there an easy way to get the count of records(result set) of the output.

I cant use sysindexes b'z i need the count of the output genereted by the SQl Statement which joins many tables and retrieves many rows.

Thanks,
Sajai

View 2 Replies View Related

MS SQL Slow Down

Mar 24, 2006

Hi fellas (and girllas),

Got a problem (duh!). My MSSQL Server lags. Now, mind, it doesn't lag all the time. And it seems to be independent of the # of users trying to access the server. And it random clears itself up. And the problem doesn't present itself in SQL MGR, just on the web app we're running on it.

Setup:
SQL Server 2k running on 2k3 w/ IIS & backup exec.
All SQL data files are on a raid5 SCSI U160.

App:
Intranet App developed by us for us. ASP.NET & VB.NET.

Symptoms:
When queried server takes a LONG time to respond. So long infact it has become counter productive. When taking a look at the server, the CPU usage hovers between 50-75% and spikes up to 90% every now and then just for kicks. The memory usage is 2.35gb out of 4gb. To fix this we have to kill and restart all the SQL services.

Any thoughts on what to look at? There're indexes on the required FKs and the heavily queried columns. We're at a loss here.

Thanks for any helpful help!

=Me!

View 3 Replies View Related

MS SQL Very Slow

Apr 7, 2006

Database has its few HUGE tables, but I experienced queries against few other very small tables to be incredible slow.

Anybody has the same or similar problem?

View 1 Replies View Related

App Slow.

Apr 15, 2008

A vendor's application is performing slow. Vendor tested it in QA and it's slow. End-users run it in PRD and it's slow. The application calls SP1, and SP1 calls SP2. Inside SP1 has a cursor. I believe as the db gets larger. The application is going to be even slower. What can I suggest to the vendor in order to fix it? Tell them to re-write the application code? Eliminate cursor?

Thanks

View 9 Replies View Related

Why Is My CTE Very Slow ?

May 27, 2008

Hello,

I have 4000 record in my table employee. it takes 13 sec to get data. It this normal ? What is wrong ?

Thanks

Code Snippet

CREATE FUNCTION [dbo].[VrniStrukturo] (@id_sod int)
RETURNS TABLE
AS
RETURN
(
WITH tree(id, parent_id, naziv, nivo) AS
(
-- Base case
SELECT
id,
parent_id,
naziv,
1 as nivo
FROM employee
WHERE id = @id_sod

UNION ALL

-- Recursive step
SELECT
e.id,
e.parent_id,
e.naziv,
eh.nivo + 1 AS nivo
FROM employee e
INNER JOIN tree eh ON
e.parent_id = eh.id
)

--SELECT *
SELECT id
FROM tree
--ORDER BY nivo, priimek, ime
);

View 5 Replies View Related







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