3000 rows = 45 sec

I've got a table <Person> with 70 columns
that contains about 3000 rows

It takes 45 sec in the QA to run <Select * From Person>

3000 rows = 45 sec = Poor performances

So how in the heck do you all do to run
<Select * From MillionPersonsTable>

You've got superdoopers alien machines ?

(sorry, i've got terrible perf problems)Check for activity, locks on your server, try just select count(*) from your table or select one column from your table. It is impossible to have such performance on sql server.|||Do you servers have dual processors, and multiple hard drives?|||pentium III or IV|||select count(*) takes 3 secs
select column takes 3 secs too

I tought that I had poor perfs on Sybase
But they're practically the same on SQL Server|||How much data are you returning to the client?

select dpages/128 as KB
from sysindexes
where id = object_id('yourtable')
and indid in (0, 1)

70 columns sounds awfully wide.|||1 KB|||Heh. Small problem with units. Guess that's why I don't work at NASA...

You are returning somewhere between 1 and 2MB of data to the client. Still 45 seconds sounds a bit long for 1MB of data to wiggle its way through your cabling. Is the server in your building, or remote?|||the server is in the building|||The select you did above on a single column. Was that an indexed column? If so, how log does it take to return an unindexed column?|||3 secs ...
for indexed or non indexed columns|||OK. Last thought.

Open a QA session to the server, and note the SPID number (bottom right). In this window run the select * from table query. In a second window run the following:

select spid, cpu, physical_io, memusage, waittype, lastwaittype, blocked, waitresource
from master..sysprocesses
where spid = (your spid)

Blocked should always be 0 (so if it is not, you have an answer right there).
Lastwaittype is the type of waiting the connection did last, so it will not change often.
Waittype is a binary field that is the current waittype of the connection. You can find some of the definitions in KB article Q244455. 0x800 is network IO, I believe.
Physical_IO is the one I suspect you will see jump. If this jumps early in the query, then you are reading the table from disk. Table scans tend to recylcle memory this way, to keep more efficient tables in memory. If you have outrageous physical_io, you can look at trying to break up the table, or simply not do select * on it without a where clause.|||I'll check that tomorrow.

Thank you for all your time

Caroline|||Are you running any traces ? Is sql server agent running ?|||Run dbcc showcontig on the table, I bet you have high fragmentation. If you do not have a clustered index on the table, you should. There are rarely times that a table should not have a clustered index.

HTH|||dbcc showcontig

Table : 'T74PERS' (453576654); index ID = 1, base de donnes ID = 9
Analyse du niveau TABLE effectue.
- Pages analyses........................: 213
- extensions analyses.....................: 28
- extensions commutes....................: 28
- Moy des pages par extension............: 7.6
- Densit d'analyse [meilleure valeur du compte rel]......: 93.10% [27:29]
- Fragmentation d'analyse logique..: 12.68%
- Fragmentation d'analyse d'extension..: 3.57%
- Moy octets libres par page................: 389.8
- Densit de page moy (pleine).........: 95.18%

Is 12.68% and 3.57% high fragmentation ?|||I've got a clustered index on one of the columns of the table|||Your numbers look OK to me. Don't think it is an frag problem

3 table left outer join

I am joining 3 tables. 1st table I want all rows; I also want all rows in table 1 not in table 2 or in Table 2 but have a blank journal id and all rows in table 1 not in table 3. HELP!

SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
FROM PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
AND D.INVOICE = A.INVOICE
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.INVOICE = C.ITEM
WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
AND B.JOURNAL_ID = ' 'could you post your query again, there seems to be a typo in it

AND A.INVOICE = C.ITEM
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B

the PS_BI_HDR D is wrong where it is

rudy|||SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM)
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
AND D.INVOICE = A.INVOICE
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.INVOICE = C.ITEM
WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
AND B.JOURNAL_ID = ' '

This is the query. Note: ITEM and INVOICE are on in the same on two different tables. I forgot the ().|||[QUOTE][SIZE=1]Originally posted by AMYC
SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM)
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
AND D.INVOICE = B.INVOICE
WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
AND B.JOURNAL_ID = ' '

Update; found one error ;)|||i still see a syntax error in the following snippet of code --

FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM)
PS_BI_HDR D LEFT OUTER JOIN ...

this is structurally the same as

FROM (derived table) foo bar LEFT OUTER JOIN ...

you can give the derived table one alias name but not two

rudy|||Thanks for your help but I feel like I have bigger issues than syntax errors. It's more structural. This is a new approach I am trying but I still get rows I should not ie rows from BI_ACCT_ENTRY that should not be returned. THanks!

SELECT A.BUSINESS_UNIT, A.INVOICE
FROM PS_BI_HDR A WHERE (NOT EXISTS (SELECT B.GROUP_BU, B.ITEM FROM PS_PENDING_ITEM_FS B
WHERE A.BUSINESS_UNIT = B.GROUP_BU AND A.INVOICE = B.ITEM)
AND NOT EXISTS(SELECT BUSINESS_UNIT, INVOICE FROM PS_BI_ACCT_ENTRY C
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INVOICE = C.INVOICE))
OR EXISTS (SELECT BUSINESS_UNIT, INVOICE FROM PS_BI_ACCT_ENTRY C
WHERE JOURNAL_ID = ' ')|||i don't mean to criticize but it will help you if you get in the habit of formatting your code like this:

SELECT A.BUSINESS_UNIT
, A.INVOICE
FROM PS_BI_HDR A
WHERE (
NOT EXISTS
( SELECT B.GROUP_BU
, B.ITEM
FROM PS_PENDING_ITEM_FS B
WHERE A.BUSINESS_UNIT = B.GROUP_BU
AND A.INVOICE = B.ITEM )
AND NOT EXISTS
( SELECT BUSINESS_UNIT
, INVOICE
FROM PS_BI_ACCT_ENTRY C
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.INVOICE = C.INVOICE )
)
OR EXISTS
( SELECT BUSINESS_UNIT
, INVOICE
FROM PS_BI_ACCT_ENTRY C
WHERE JOURNAL_ID = ' ')

now you can plainly see that you will get PS_BI_HDR records if any PS_BI_ACCT_ENTRY entries exist with a blank JOURNAL_ID

was that your problem?

by the way, i would not alias the table in the 3rd subquery with C because you've already used that in the 2nd subquery -- not that it causes an error, but you never know...

rudy|||I do format my code but just copying and pasting here as it is a total pain to format it on here :) anyway, I fixed it. Thanks!

SELECT A.BUSINESS_UNIT, A.INVOICE
FROM PS_BI_HDR A WHERE
EXISTS (SELECT C.BUSINESS_UNIT, C.INVOICE FROM
PS_BI_ACCT_ENTRY C
WHERE JOURNAL_ID = ' '
AND NOT EXISTS (SELECT B.GROUP_BU, B.ITEM FROM
PS_PENDING_ITEM_FS B
WHERE A.BUSINESS_UNIT = B.GROUP_BU
AND A.INVOICE = B.ITEM)
OR NOT EXISTS(SELECT BUSINESS_UNIT,
INVOICE FROM PS_BI_ACCT_ENTRY C
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.INVOICE = C.INVOICE)
ORDER BY A.INVOICE|||it is a total pain to format it on here
not really

just wrap it in code tags -- i typically type them, but you can also use the number/hash sign # button (between the IMG and PHP buttons when you create a post)|||I didn't realize I could do that. Thanks!!sql

2005 SMS, how do I open a table

Using 2005 SQL Management Studio, how do I open a table without selecting all
rows? When I right-click a table in Object Explorer, I only see Open, along
with some other operations.
I expected to see the choices that are in SQL 2000 EE (Return All Rows,
Return Top... & Query).
Cheers - & Happy Thansgiving!You can't using the Object Explorer GUI (of course you can just write a
query and run that). File a suggestion at
http://lab.msdn.microsoft.com/productfeedback/default.aspx
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
news:E475FECB-E032-4825-8C51-96940617D89D@.microsoft.com...
> Using 2005 SQL Management Studio, how do I open a table without selecting
> all
> rows? When I right-click a table in Object Explorer, I only see Open,
> along
> with some other operations.
> I expected to see the choices that are in SQL 2000 EE (Return All Rows,
> Return Top... & Query).
> Cheers - & Happy Thansgiving!

2005 Merge Agent Degradation

I have a merge agent running extremely slow - schedule fires every 1 minute,
however it takes 1 to 3 minutes to execute. Delivery Rate 5 rows/sec or less,
all 5 other merge agents execute in less than 20 seconds and Delivery Rate 80
rows/sec or greater.
Each time merge agent fires, subscr cpu spikes to 85% - normally 15 to 20%.
Ran profiler and discovered this stamement below hogging the cpu and having
the longest duration.
I am rebuilding the MSmerge_genhistory table nightly w/ the following: DBCC
DBREINDEX (MSmerge_genhistory, '', 80)
Any ideas? A re-init will resolve problem , have had to do this many times
in past - at least every 30 days... however, want to get to root cause. tia
Chris
select top (@.numgens) *
from
(
select generation, guidsrc, art_nick,
case when genstatus = 4 then 0 else genstatus end as
genstatus,
pubid, nicknames,
okaytoskip = case when
art_nick is not null and art_nick <> 0
and genstatus in (0,4)
-- Skip all rows that are for incomplete
generations for articles that have no joins.
and not exists (select 1 from
dbo.sysmergesubsetfilters where (join_nickname = art_nick or art_nickname =
art_nick) and (filter_type & 1) = 1)
then 1 else 0 end
, changecount
from
(select generation, guidsrc, art_nick, genstatus, pubid,
nicknames, changecount
from dbo.MSmerge_genhistory with (rowlock, repeatableread)
where generation >= @.genstart
and generation <= @.maxgen_to_enumerate
and generation > @.mingen_to_enumerate
and (art_nick = 0 or art_nick is NULL or
art_nick in (select nickname from dbo.sysmergearticles
where pubid = @.pubid))
) as generation_range
UNION ALL -- use UNION ALL instead of UNION for perf
reasons. Merge agent code will skip dupes. Will only have max 2 dupes.
select generation = @.next_possible_watermark, guidsrc =
@.next_possible_watermark_guidsrc,
art_nick = @.next_possible_watermark_art_nick, genstatus =
@.next_possible_watermark_genstatus,
pubid = @.next_possible_watermark_pubid, nicknames =
@.next_possible_watermark_nicknames,
okaytoskip = 0, changecount =
@.next_possible_watermark_changecount
where @.next_possible_watermark is not null
union all
select generation = @.min_open_gen, guidsrc= @.min_open_gen_guid,
art_nick=@.min_open_gen_art_nick, genstatus=0,
pubid=@.pubid, nicknames=NULL, okaytoskip = 0,
changecount=0
where @.min_open_gen is not null
) as genertions
order by generation ASC
Two things I can suggest here is:
1. Avoid metadata contention. Make sure merge agents do not run at the
same time so stagger their schedules.
2. Retention period can cause lots of data to be stored in the contets
and tombstone tables. Default i think is 14 days. So 14 days worth of
changes are stored. Consider reducing this if subscribers are not
offline for a long time.
Regards Jim
http://jims-spanakopita.blogspot.com/
|||What do your join filters look like? By placing indexes on all the filters
you will get better performance. Another option to try is to modify your
filters to make them as shallow as possible.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4ED9E8B8-9CA1-4B10-A609-E67921202C68@.microsoft.com...
> I have a merge agent running extremely slow - schedule fires every 1
> minute,
> however it takes 1 to 3 minutes to execute. Delivery Rate 5 rows/sec or
> less,
> all 5 other merge agents execute in less than 20 seconds and Delivery Rate
> 80
> rows/sec or greater.
> Each time merge agent fires, subscr cpu spikes to 85% - normally 15 to
> 20%.
> Ran profiler and discovered this stamement below hogging the cpu and
> having
> the longest duration.
> I am rebuilding the MSmerge_genhistory table nightly w/ the following:
> DBCC
> DBREINDEX (MSmerge_genhistory, '', 80)
>
> Any ideas? A re-init will resolve problem , have had to do this many times
> in past - at least every 30 days... however, want to get to root cause.
> tia
> Chris
>
> select top (@.numgens) *
> from
> (
> select generation, guidsrc, art_nick,
> case when genstatus = 4 then 0 else genstatus end as
> genstatus,
> pubid, nicknames,
> okaytoskip = case when
> art_nick is not null and art_nick <> 0
> and genstatus in (0,4)
> -- Skip all rows that are for incomplete
> generations for articles that have no joins.
> and not exists (select 1 from
> dbo.sysmergesubsetfilters where (join_nickname = art_nick or art_nickname
> =
> art_nick) and (filter_type & 1) = 1)
> then 1 else 0 end
> , changecount
> from
> (select generation, guidsrc, art_nick, genstatus, pubid,
> nicknames, changecount
> from dbo.MSmerge_genhistory with (rowlock, repeatableread)
> where generation >= @.genstart
> and generation <= @.maxgen_to_enumerate
> and generation > @.mingen_to_enumerate
> and (art_nick = 0 or art_nick is NULL or
> art_nick in (select nickname from dbo.sysmergearticles
> where pubid = @.pubid))
> ) as generation_range
> UNION ALL -- use UNION ALL instead of UNION for perf
> reasons. Merge agent code will skip dupes. Will only have max 2 dupes.
> select generation = @.next_possible_watermark, guidsrc =
> @.next_possible_watermark_guidsrc,
> art_nick = @.next_possible_watermark_art_nick, genstatus =
> @.next_possible_watermark_genstatus,
> pubid = @.next_possible_watermark_pubid, nicknames =
> @.next_possible_watermark_nicknames,
> okaytoskip = 0, changecount =
> @.next_possible_watermark_changecount
> where @.next_possible_watermark is not null
> union all
> select generation = @.min_open_gen, guidsrc= @.min_open_gen_guid,
> art_nick=@.min_open_gen_art_nick, genstatus=0,
> pubid=@.pubid, nicknames=NULL, okaytoskip = 0,
> changecount=0
> where @.min_open_gen is not null
> ) as genertions
> order by generation ASC
>