Latin to Cyrillic

Hi everybody, i'm a new SQL server user and i have a problem
I have to select a loto of cyrillic records saved on a table that has
Latin collation
How can i see them in Cyrillic?
i just tried to make another table with a column with Cyrillic
collation but it won't work
Thank you
FedericoFederico,
I guess the newsgroups on www.sql.ru is the best place to ask this
question.

Last Select Statement

Any ideas how I can find out when was the last update/select made in a database in SQL Server? Even if I can find any user login information, that would be useful too.

Regards,

Bala

With the SQL Server Profiler tool, you can monitor querying activity, including who's running the query. But you only get that information when it's running... I don't know of a general activity log SQL Server keeps that you could study.

-Ryan / Kardax

|||

You could try using Lumigent's Log Explorer or Red-Gate's Log Rescue, however these will not help with tracking SELECT statements but will help track modifications to the database's data or structure:

http://www.lumigent.com/products/le_sql.html

http://www.red-gate.com/products/SQL_Log_Rescue/index.htm

Note that the ability to obtain such information from a database's log file is dependent on both the database's recovery model and when the last log backup was taken (if appropriate).

Chris

Last row in select question

DECLARE @.LastID int
DECLARE @.RowsToSelect int

SET ROWCOUNT @.RowsToSelect
SELECT @.LastID = [id] FROM MyTable ORDER BY [id]
SET ROWCOUNT 0

I think that value of @.LastID will be equals to [id] in the last row
of select(i.e. row with number @.RowsToSelect). But I don't understand
why it's true. For example, @.LastID not necessarily equals to last row
value in the same select without 'ORDER BY [ID]'.Kurzman (max@.virtuman.com) writes:
> DECLARE @.LastID int
> DECLARE @.RowsToSelect int
> SET ROWCOUNT @.RowsToSelect
> SELECT @.LastID = [id] FROM MyTable ORDER BY [id]
> SET ROWCOUNT 0
> I think that value of @.LastID will be equals to [id] in the last row
> of select(i.e. row with number @.RowsToSelect). But I don't understand
> why it's true.

Books Online says:

If the SELECT statement returns more than one value, the variable is
assigned the last value returned.

So your observation is correct.

> For example, @.LastID not necessarily equals to last row value in the same
> select without 'ORDER BY [ID]'.

How do you know that it is not the last value? Without an ORDER BY
clause, SQL Server is free to return the rows in any order, so any value
of [id] is correct. Recall that logically tables are unordered sets of
data, so there is no first or last value in a table.

You must always specify an ORDER BY clause to impose a certain order.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Last row ?

Hello Team

How can i get a value from a column in the last row

for ex: select orderno from orders .......... but the last row ?

Thanks lot

Greetings,

SQL Server has no concept of last row, or first row or middle row for that matter.

To determine the last row you need to know by what criteria. If your Order Number column is sequential or your table contains a datetime column then you can get a close approximation by using TOP and ORDER BY.

SELECT TOP 1 OrderNo FROM Orders ORDER BY OrderDate DESC

If there is no column in the table from which you can determine the correct ordering then you're stuck.

--

Regards,
Neale NOON

|||

There is one more work around available to get the last record..

if your table has 3 columns,

declare @.col1 as varchar(100);
declare @.col2 as varchar(100);
declare @.col3 as varchar(100);

Select @.Co1l=Col1,
@.Col2=Col2,
@.Col3=Col3
From
Tablename;

Select @.Col1 as Col1, @.Col2 as Col2, @.Col3 as Col3;

So here you need not to use any order by clause. It always fet the last row.

|||If you are really just looking for the last row, reverse your sort order and use SELECT TOP 1 ... That will get you the first row in the reversed order, ie last row.

Last record in 10,000,000 rows

Hey gang, I got a table that has about 10,000,000 row and I need a test of the last 10 rows. Isn't the statement: Select bottom 10 from table?Rows in a table don't have any order. You can order the result set any way you want. Take the TOP 10 with the order "upside down" and you are in business!

-PatP|||SELECT TOP 1 * from test order by id desc
OR
select * from TEST a
where 1>(select count(*) from TEST where ID>a.id)
-SS|||Do you have an ADD_ROW_TS or IDENTITY Column?|||Gentlemen, I dont beleive I've been clear on what I'm attempting to do. I have a table with 10,000,000 records. (which happend to reflect 6 months historical data) that I need to confirm it is there. I can do a 'select top 10 from table" to get the first 10 record of this new table. I need to see the last 10 to confirm the month,date,time etc... What I've come up with in the sql anaylser is
select * from dbo.INCOMING_TEMP
where RECORD_NUMBER > 10453700
according to my records the total number of rows at 10453747

It's running but taking all day to get there.|||I'm assuming RECORD_NUMBER is defined like

RECORD_NUMBER int IDENTITY(1,1) NOT NULL

Yes?

SELECT TOP 10 * FROM yourTable99 ORDER BY RECORD_NUMBER DESC

Will get you the official last 10 records...

If that's truly the case...

No?|||Thanks that works fine. I havent been my self since I got the damn cast on my foot. So bear with me. Not I get to compare radomn samples of the data before blowing the original table away.

Last record ?

Hello Team

i want to get a value from a column in the last record

i wrote select ID from (select top 1 * desc from items)

any idea, Thanks lot

If you want to use top you have to use order by clause with desc to fetch the last record..

Code Snippet

Select Top 1 Id from Demo Order By Id Desc

or

Select max(id) from Demo;

Another easy way is,

Code Snippet

Declare @.Id as int;

Select @.ID = ID from Demo;

Select @.ID;

here it always return the last record

|||

Just select from the table ordering by the field that can be used to identify if this is a recent row or not.

Code Snippet

Select Top 1 ID

From Items

Order By ID Desc

Assuming ID is a column that is incremental for each new row. If you don't have an incremental ID, you can use a column that contains the date when the row has been added.

I hope this answers your question. If not, please post more information about the table.

Best regards,

Sami Samir

|||

Thank you very much

and Mr samy too

|||If you want to obtain the ID value of the last record inserted to a table, and then use that ID value in additional INSERTS/UPDATES, you may wish to explore using the system function: SCOPE_IDENTITY. It can return the value of the immediately preceding insert WITHOUT having to execute a query.sql

Last Queued Updating question....I think

When I setup my publication, I selected Queued updating. When I setup my Push, do I also need to select queued updating for the Subscriber as well?
Even though I have no intention of doing any updates on the Subscriber, I am only doing this because it was suggested as being the best option.
Please advise. I think this should be it for questions on this topic then....I hope.
JLS,
yes - this needs to be selected if your aim is a simple failover methodology and it'll only be available if you select the advanced options. If you don't select it then you end up with a non-updatable subscriber, which maybe isn't your main interest, but the implication of this is that you won't have automatic range management of identities on the subscriber and also no simple method of failing back to the publisher if necessary.
HTH,
Paul Ibison

Last Month calculation of selected Dates

I have a time dimension and a Last Month calculation.

In Cube Browsing, when I only select some dates in a month (e.g. 4th and 5th May 2007), at month level (May 2007), I found that the "Current Time" calculation gives me the sum of 4th May and 5th May. However, my LM calcuation gives me the total sum of LM value for the whole May 2007.

My LM calculation is as follows:

[Time].[Month Comparison].DefaultMember,
ParallelPeriod(
[Time].[Date with Month].[Month],
1,
[Time].[Date with Month].CurrentMember
)

Can anyone tell me how can I archive the calculation of LM at month level that only sum up the LM-value of the selected dates?

Thanks in advance.

Sorry, but I'm a little confused about what it is you are calculating. Could you provide more info, maybe a full MDX statement or query?

Thanks, Bryan

Last Month calculation of selected Dates

I have a time dimension and a Last Month calculation.

In Cube Browsing, when I only select some dates in a month (e.g. 4th and 5th May 2007), at month level (May 2007), I found that the "Current Time" calculation gives me the sum of 4th May and 5th May. However, my LM calcuation gives me the total sum of LM value for the whole May 2007.

My LM calculation is as follows:

[Time].[Month Comparison].DefaultMember,
ParallelPeriod(
[Time].[Date with Month].[Month],
1,
[Time].[Date with Month].CurrentMember
)

Can anyone tell me how can I archive the calculation of LM at month level that only sum up the LM-value of the selected dates?

Thanks in advance.

Sorry, but I'm a little confused about what it is you are calculating. Could you provide more info, maybe a full MDX statement or query?

Thanks, Bryan

large table update

hello, i have a following problem: I have got 130 million row TABLEA
and I need to run this update:
UPDATE TABLEA
SET COLUMNA=(SELECT COLUMNB FROM TABLEB WHERE condition)

I suppose that for every row from TABLEA (80 million times) select statement must be executed, which is terribly slow. Isn't there a better way to write this update? Thank youCould you provide the WHERE clause in detail, pls?|||Originally posted by Coolberg
Could you provide the WHERE clause in detail, pls?

WHERE column from TABLEA=column FROM TABLEB

Large table query 99% results streamed quickly, huge pause for las

My Query looks like this.
select * from table
where field = 'abc'
In SQL Server 2005 I see that there is some new data retrieval mechanism in
the query analyser tool so that results are streamed in. What I mean is that
if I do a query on my large table I get first 1000 rows in about 1 seconds,
and next 1000 in the next 1s and this continues right up to about 2 mins ,
where I have about 29000 rows sitting in query analyser, but it hasn't
finished yet, it sits there churning for another 10 mins before it finishes
and the total row count is 29052 records.
I don't understand why 99% of results are returned quickly (15% of the time)
and then the last 1% of the results are returned in a huge amount of time
(85%)
In SQL 2000 doing this query has query analyser sitting there bored for
quite a while.
I have changed the query to return a smaller set of data like 15000 records
and the same thing happens where it returns 99% of records quite quickly but
the last 1% of data still takes about 10 mins to return.
I have tried creating a stored procedure to do this thinking it might work
better but no, and have tried doing a top 100 PERCENT with no success either.
It's difficult to speculate without knowing what your query and data look
like. A likely possibility is you're returning 29000 records out of a few
million and the records you selected are toward the front of the table scan
order. When you retrieve that many records the query processor likely will
be doing a table scan and unless there's something in the query to indicate
when it's done, every row in the table will be read. Looking at the query
plan will help you understand what the query processor is doing.
How are you determining how many records have been returned? Are you
actually scrolling through the data so you know the records have been
returned and rendered in the UI? Do you get different results if you
display the results as text?
I have to ask - do you really read 29000 records in Management Studio? Even
if you read a record a second that's almost an hour of reading. If you're
pulling back that many records to page through a look at a few records, you
using a lot of processing power, memory, and bandwidth on the server for no
reason.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> My Query looks like this.
> select * from table
> where field = 'abc'
> In SQL Server 2005 I see that there is some new data retrieval mechanism
> in
> the query analyser tool so that results are streamed in. What I mean is
> that
> if I do a query on my large table I get first 1000 rows in about 1
> seconds,
> and next 1000 in the next 1s and this continues right up to about 2 mins ,
> where I have about 29000 rows sitting in query analyser, but it hasn't
> finished yet, it sits there churning for another 10 mins before it
> finishes
> and the total row count is 29052 records.
> I don't understand why 99% of results are returned quickly (15% of the
> time)
> and then the last 1% of the results are returned in a huge amount of time
> (85%)
> In SQL 2000 doing this query has query analyser sitting there bored for
> quite a while.
> I have changed the query to return a smaller set of data like 15000
> records
> and the same thing happens where it returns 99% of records quite quickly
> but
> the last 1% of data still takes about 10 mins to return.
> I have tried creating a stored procedure to do this thinking it might work
> better but no, and have tried doing a top 100 PERCENT with no success
> either.
|||Thanks for the reply, I agree ... I think it is doing a full table scan.
I am determining the number of records being returned by looking at the
bottom of the management studio where it tells you how many records are
returned, also when it has a record id column, which I see increments for
each new row of data is returned. Yes I am scrolling through the data.
Display the results as text? Not sure what you mean there sorry.
I am not reading the records. Just wanting to see how long it takes to
retrieve different sets of data from this table.
"Roger Wolter[MSFT]" wrote:

> It's difficult to speculate without knowing what your query and data look
> like. A likely possibility is you're returning 29000 records out of a few
> million and the records you selected are toward the front of the table scan
> order. When you retrieve that many records the query processor likely will
> be doing a table scan and unless there's something in the query to indicate
> when it's done, every row in the table will be read. Looking at the query
> plan will help you understand what the query processor is doing.
> How are you determining how many records have been returned? Are you
> actually scrolling through the data so you know the records have been
> returned and rendered in the UI? Do you get different results if you
> display the results as text?
> I have to ask - do you really read 29000 records in Management Studio? Even
> if you read a record a second that's almost an hour of reading. If you're
> pulling back that many records to page through a look at a few records, you
> using a lot of processing power, memory, and bandwidth on the server for no
> reason.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
>
|||If you're not actually looking at the results as they come in, you may be
seeing a UI painting delay. The results may all be retrieved and in memory
but not rendered in the grid yet. If you look at the Management Studio
options and change the result display option to text or a file instead of
grid, the results will appear significantly faster. Bottom line is that
with that many results, creating and formatting the grid cells generally
takes more time than retrieving the results.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:89E01783-910F-4549-94BB-1567701DB175@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, I agree ... I think it is doing a full table scan.
> I am determining the number of records being returned by looking at the
> bottom of the management studio where it tells you how many records are
> returned, also when it has a record id column, which I see increments for
> each new row of data is returned. Yes I am scrolling through the data.
> Display the results as text? Not sure what you mean there sorry.
> I am not reading the records. Just wanting to see how long it takes to
> retrieve different sets of data from this table.
> "Roger Wolter[MSFT]" wrote:

Large table query 99% results streamed quickly, huge pause for las

My Query looks like this.
select * from table
where field = 'abc'
In SQL Server 2005 I see that there is some new data retrieval mechanism in
the query analyser tool so that results are streamed in. What I mean is that
if I do a query on my large table I get first 1000 rows in about 1 seconds,
and next 1000 in the next 1s and this continues right up to about 2 mins ,
where I have about 29000 rows sitting in query analyser, but it hasn't
finished yet, it sits there churning for another 10 mins before it finishes
and the total row count is 29052 records.
I don't understand why 99% of results are returned quickly (15% of the time)
and then the last 1% of the results are returned in a huge amount of time
(85%)
In SQL 2000 doing this query has query analyser sitting there bored for
quite a while.
I have changed the query to return a smaller set of data like 15000 records
and the same thing happens where it returns 99% of records quite quickly but
the last 1% of data still takes about 10 mins to return.
I have tried creating a stored procedure to do this thinking it might work
better but no, and have tried doing a top 100 PERCENT with no success either.It's difficult to speculate without knowing what your query and data look
like. A likely possibility is you're returning 29000 records out of a few
million and the records you selected are toward the front of the table scan
order. When you retrieve that many records the query processor likely will
be doing a table scan and unless there's something in the query to indicate
when it's done, every row in the table will be read. Looking at the query
plan will help you understand what the query processor is doing.
How are you determining how many records have been returned? Are you
actually scrolling through the data so you know the records have been
returned and rendered in the UI? Do you get different results if you
display the results as text?
I have to ask - do you really read 29000 records in Management Studio? Even
if you read a record a second that's almost an hour of reading. If you're
pulling back that many records to page through a look at a few records, you
using a lot of processing power, memory, and bandwidth on the server for no
reason.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> My Query looks like this.
> select * from table
> where field = 'abc'
> In SQL Server 2005 I see that there is some new data retrieval mechanism
> in
> the query analyser tool so that results are streamed in. What I mean is
> that
> if I do a query on my large table I get first 1000 rows in about 1
> seconds,
> and next 1000 in the next 1s and this continues right up to about 2 mins ,
> where I have about 29000 rows sitting in query analyser, but it hasn't
> finished yet, it sits there churning for another 10 mins before it
> finishes
> and the total row count is 29052 records.
> I don't understand why 99% of results are returned quickly (15% of the
> time)
> and then the last 1% of the results are returned in a huge amount of time
> (85%)
> In SQL 2000 doing this query has query analyser sitting there bored for
> quite a while.
> I have changed the query to return a smaller set of data like 15000
> records
> and the same thing happens where it returns 99% of records quite quickly
> but
> the last 1% of data still takes about 10 mins to return.
> I have tried creating a stored procedure to do this thinking it might work
> better but no, and have tried doing a top 100 PERCENT with no success
> either.|||Thanks for the reply, I agree ... I think it is doing a full table scan.
I am determining the number of records being returned by looking at the
bottom of the management studio where it tells you how many records are
returned, also when it has a record id column, which I see increments for
each new row of data is returned. Yes I am scrolling through the data.
Display the results as text? Not sure what you mean there sorry.
I am not reading the records. Just wanting to see how long it takes to
retrieve different sets of data from this table.
"Roger Wolter[MSFT]" wrote:
> It's difficult to speculate without knowing what your query and data look
> like. A likely possibility is you're returning 29000 records out of a few
> million and the records you selected are toward the front of the table scan
> order. When you retrieve that many records the query processor likely will
> be doing a table scan and unless there's something in the query to indicate
> when it's done, every row in the table will be read. Looking at the query
> plan will help you understand what the query processor is doing.
> How are you determining how many records have been returned? Are you
> actually scrolling through the data so you know the records have been
> returned and rendered in the UI? Do you get different results if you
> display the results as text?
> I have to ask - do you really read 29000 records in Management Studio? Even
> if you read a record a second that's almost an hour of reading. If you're
> pulling back that many records to page through a look at a few records, you
> using a lot of processing power, memory, and bandwidth on the server for no
> reason.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> > My Query looks like this.
> > select * from table
> > where field = 'abc'
> >
> > In SQL Server 2005 I see that there is some new data retrieval mechanism
> > in
> > the query analyser tool so that results are streamed in. What I mean is
> > that
> > if I do a query on my large table I get first 1000 rows in about 1
> > seconds,
> > and next 1000 in the next 1s and this continues right up to about 2 mins ,
> > where I have about 29000 rows sitting in query analyser, but it hasn't
> > finished yet, it sits there churning for another 10 mins before it
> > finishes
> > and the total row count is 29052 records.
> >
> > I don't understand why 99% of results are returned quickly (15% of the
> > time)
> > and then the last 1% of the results are returned in a huge amount of time
> > (85%)
> >
> > In SQL 2000 doing this query has query analyser sitting there bored for
> > quite a while.
> >
> > I have changed the query to return a smaller set of data like 15000
> > records
> > and the same thing happens where it returns 99% of records quite quickly
> > but
> > the last 1% of data still takes about 10 mins to return.
> >
> > I have tried creating a stored procedure to do this thinking it might work
> > better but no, and have tried doing a top 100 PERCENT with no success
> > either.
>|||If you're not actually looking at the results as they come in, you may be
seeing a UI painting delay. The results may all be retrieved and in memory
but not rendered in the grid yet. If you look at the Management Studio
options and change the result display option to text or a file instead of
grid, the results will appear significantly faster. Bottom line is that
with that many results, creating and formatting the grid cells generally
takes more time than retrieving the results.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:89E01783-910F-4549-94BB-1567701DB175@.microsoft.com...
> Thanks for the reply, I agree ... I think it is doing a full table scan.
> I am determining the number of records being returned by looking at the
> bottom of the management studio where it tells you how many records are
> returned, also when it has a record id column, which I see increments for
> each new row of data is returned. Yes I am scrolling through the data.
> Display the results as text? Not sure what you mean there sorry.
> I am not reading the records. Just wanting to see how long it takes to
> retrieve different sets of data from this table.
> "Roger Wolter[MSFT]" wrote:
>> It's difficult to speculate without knowing what your query and data look
>> like. A likely possibility is you're returning 29000 records out of a
>> few
>> million and the records you selected are toward the front of the table
>> scan
>> order. When you retrieve that many records the query processor likely
>> will
>> be doing a table scan and unless there's something in the query to
>> indicate
>> when it's done, every row in the table will be read. Looking at the
>> query
>> plan will help you understand what the query processor is doing.
>> How are you determining how many records have been returned? Are you
>> actually scrolling through the data so you know the records have been
>> returned and rendered in the UI? Do you get different results if you
>> display the results as text?
>> I have to ask - do you really read 29000 records in Management Studio?
>> Even
>> if you read a record a second that's almost an hour of reading. If
>> you're
>> pulling back that many records to page through a look at a few records,
>> you
>> using a lot of processing power, memory, and bandwidth on the server for
>> no
>> reason.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
>> > My Query looks like this.
>> > select * from table
>> > where field = 'abc'
>> >
>> > In SQL Server 2005 I see that there is some new data retrieval
>> > mechanism
>> > in
>> > the query analyser tool so that results are streamed in. What I mean
>> > is
>> > that
>> > if I do a query on my large table I get first 1000 rows in about 1
>> > seconds,
>> > and next 1000 in the next 1s and this continues right up to about 2
>> > mins ,
>> > where I have about 29000 rows sitting in query analyser, but it hasn't
>> > finished yet, it sits there churning for another 10 mins before it
>> > finishes
>> > and the total row count is 29052 records.
>> >
>> > I don't understand why 99% of results are returned quickly (15% of the
>> > time)
>> > and then the last 1% of the results are returned in a huge amount of
>> > time
>> > (85%)
>> >
>> > In SQL 2000 doing this query has query analyser sitting there bored for
>> > quite a while.
>> >
>> > I have changed the query to return a smaller set of data like 15000
>> > records
>> > and the same thing happens where it returns 99% of records quite
>> > quickly
>> > but
>> > the last 1% of data still takes about 10 mins to return.
>> >
>> > I have tried creating a stored procedure to do this thinking it might
>> > work
>> > better but no, and have tried doing a top 100 PERCENT with no success
>> > either.

Large table query 99% results streamed quickly, huge pause for las

My Query looks like this.
select * from table
where field = 'abc'
In SQL Server 2005 I see that there is some new data retrieval mechanism in
the query analyser tool so that results are streamed in. What I mean is tha
t
if I do a query on my large table I get first 1000 rows in about 1 seconds,
and next 1000 in the next 1s and this continues right up to about 2 mins ,
where I have about 29000 rows sitting in query analyser, but it hasn't
finished yet, it sits there churning for another 10 mins before it finishes
and the total row count is 29052 records.
I don't understand why 99% of results are returned quickly (15% of the time)
and then the last 1% of the results are returned in a huge amount of time
(85%)
In SQL 2000 doing this query has query analyser sitting there bored for
quite a while.
I have changed the query to return a smaller set of data like 15000 records
and the same thing happens where it returns 99% of records quite quickly but
the last 1% of data still takes about 10 mins to return.
I have tried creating a stored procedure to do this thinking it might work
better but no, and have tried doing a top 100 PERCENT with no success either
.It's difficult to speculate without knowing what your query and data look
like. A likely possibility is you're returning 29000 records out of a few
million and the records you selected are toward the front of the table scan
order. When you retrieve that many records the query processor likely will
be doing a table scan and unless there's something in the query to indicate
when it's done, every row in the table will be read. Looking at the query
plan will help you understand what the query processor is doing.
How are you determining how many records have been returned? Are you
actually scrolling through the data so you know the records have been
returned and rendered in the UI? Do you get different results if you
display the results as text?
I have to ask - do you really read 29000 records in Management Studio? Even
if you read a record a second that's almost an hour of reading. If you're
pulling back that many records to page through a look at a few records, you
using a lot of processing power, memory, and bandwidth on the server for no
reason.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> My Query looks like this.
> select * from table
> where field = 'abc'
> In SQL Server 2005 I see that there is some new data retrieval mechanism
> in
> the query analyser tool so that results are streamed in. What I mean is
> that
> if I do a query on my large table I get first 1000 rows in about 1
> seconds,
> and next 1000 in the next 1s and this continues right up to about 2 mins ,
> where I have about 29000 rows sitting in query analyser, but it hasn't
> finished yet, it sits there churning for another 10 mins before it
> finishes
> and the total row count is 29052 records.
> I don't understand why 99% of results are returned quickly (15% of the
> time)
> and then the last 1% of the results are returned in a huge amount of time
> (85%)
> In SQL 2000 doing this query has query analyser sitting there bored for
> quite a while.
> I have changed the query to return a smaller set of data like 15000
> records
> and the same thing happens where it returns 99% of records quite quickly
> but
> the last 1% of data still takes about 10 mins to return.
> I have tried creating a stored procedure to do this thinking it might work
> better but no, and have tried doing a top 100 PERCENT with no success
> either.

Large report export to pdf = corruption error

Hi,
I have a report which can produce 220 pages if the user select "all" in my
multiple choice list.
if the user try to export this report the PDF file is corrupted.
the same report with only a small amount of item selected instead-of the
"all" (so around 1 to 20 pages) is correct.
The export works fine.
any idea why?
Thanks.
Jerome.On Mar 7, 10:54 am, "Jeje" <willg...@.hotmail.com> wrote:
> Hi,
> I have a report which can produce 220 pages if the user select "all" in my
> multiple choice list.
> if the user try to export this report the PDF file is corrupted.
> the same report with only a small amount of item selected instead-of the
> "all" (so around 1 to 20 pages) is correct.
> The export works fine.
> any idea why?
> Thanks.
> Jerome.
I've never experience this issue; however, it sounds like a rendering
problem w/the quantity of data being exported to PDF. Have you tried
splitting up the results via the report query/stored procedure to
obtain a manageable amount of exported data? This might be a viable
solution.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I'm trying to create another report with less pages and a better query.
if this doesn't works, we'll change the prompt options to restrict the user
to 1 value only.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1173361141.038474.105280@.n33g2000cwc.googlegroups.com...
> On Mar 7, 10:54 am, "Jeje" <willg...@.hotmail.com> wrote:
>> Hi,
>> I have a report which can produce 220 pages if the user select "all" in
>> my
>> multiple choice list.
>> if the user try to export this report the PDF file is corrupted.
>> the same report with only a small amount of item selected instead-of the
>> "all" (so around 1 to 20 pages) is correct.
>> The export works fine.
>> any idea why?
>> Thanks.
>> Jerome.
>
> I've never experience this issue; however, it sounds like a rendering
> problem w/the quantity of data being exported to PDF. Have you tried
> splitting up the results via the report query/stored procedure to
> obtain a manageable amount of exported data? This might be a viable
> solution.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>

Large number of rows.

Hi all,

A select query returns around 1 million rows. The column in the WHERE condition is indexed. This query takes nearly 1 minute for returning the all the records. Is this normal ?

Does the number of records returned affect the performance inspite of the indexing ?

Thanks,

DBLearner

The index simply allows it to find the data it has to retrieve quickly. It then has to:

load that actual record data in from disk. Depending upon how the records are split across datapages this could be a lot of disk access. Then if you have any sorting or grouping/aggregating on the result it has to do this before it can start passing the results back. After that it has to transfer the data that you are retrieving across the link (shared memory if you are running on the server across the network if not). If you are retrieving say 20 bytes per record (quite small: a couple of ints, a bit of text and a real number can be this size or larger easily) then for a million records that is 20 megabytes.|||

The major 'chokepoints' will be the quality of the index vs. the WHERE clause criteria, amount of server memory, other activity, CPU power, and disk 'arrangement'. (Having the TempDb database on a dedicated array or LUN, for example.)

You might benefit from some tuning and optimization and perhaps that will increase the query responsiveness. But you definitely need to get some benchmarks in place.

Here is some information about Performance Audits, Monitoring, and Tuning:

Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Performance -Link Server Performance Tips
http://www.sql-server-performance.com/linked_server.asp

Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/ss_performance_monitoring.asp Practical Solution for Monitoring
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking

Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp

Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp