Data Retrieval is Slow

I have a database with a table that holds 30 million rows. Clients have
access to only those records that concern them. The problem is they want to
have access to all their data at once with the largest resultset
returning1.5 million rows.
Having toyed with the indexes, performance monitors and the like, I still
can't get the data to the client fast enough.
Could you offer any advice on what I should do?
Thanks
'Wale
wale wrote:

> I have a database with a table that holds 30 million rows. Clients have
> access to only those records that concern them. The problem is they want to
> have access to all their data at once with the largest resultset
> returning1.5 million rows.
> Having toyed with the indexes, performance monitors and the like, I still
> can't get the data to the client fast enough.
> Could you offer any advice on what I should do?
> Thanks
> 'Wale
>
LOL. Manage their expectations? ;)
But, realistically, how can anyone do anything practical with 1.5
million rows at once? There must be some better way of presenting the
data without giving it all to them at once (obviously I'm making this
statement with absolutely no knowledge of your situation so I could be
full of it ;) )
Zach
sql

Data Retrieval is Slow

I have a database with a table that holds 30 million rows. Clients have
access to only those records that concern them. The problem is they want to
have access to all their data at once with the largest resultset
returning1.5 million rows.
Having toyed with the indexes, performance monitors and the like, I still
can't get the data to the client fast enough.
Could you offer any advice on what I should do?
Thanks
'Walewale wrote:

> I have a database with a table that holds 30 million rows. Clients have
> access to only those records that concern them. The problem is they want
to
> have access to all their data at once with the largest resultset
> returning1.5 million rows.
> Having toyed with the indexes, performance monitors and the like, I still
> can't get the data to the client fast enough.
> Could you offer any advice on what I should do?
> Thanks
> 'Wale
>
LOL. Manage their expectations? ;)
But, realistically, how can anyone do anything practical with 1.5
million rows at once? There must be some better way of presenting the
data without giving it all to them at once (obviously I'm making this
statement with absolutely no knowledge of your situation so I could be
full of it ;) )
Zach

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my local
machine it takes 14 minutes (slams my machine) to get back the whole dataset.
What could be happening.
Comments Please.
Thank you
Bringing back 3.5 million rows will take time. Who could make use of such many rows.
Start by retrieving only the data you need, then tune the query by adding indexes etc to get better
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you
|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:

> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my loca
l
machine it takes 14 minutes (slams my machine) to get back the whole dataset
.
What could be happening.
Comments Please.
Thank youBringing back 3.5 million rows will take time. Who could make use of such ma
ny rows.
Start by retrieving only the data you need, then tune the query by adding in
dexes etc to get better
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my lo
cal
> machine it takes 14 minutes (slams my machine) to get back the whole datas
et.
> What could be happening.
> Comments Please.
> Thank you|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:

> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my lo
cal
> machine it takes 14 minutes (slams my machine) to get back the whole datas
et.
> What could be happening.
> Comments Please.
> Thank you

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my local
machine it takes 14 minutes (slams my machine) to get back the whole dataset.
What could be happening.
Comments Please.
Thank youBringing back 3.5 million rows will take time. Who could make use of such many rows.
Start by retrieving only the data you need, then tune the query by adding indexes etc to get better
execution times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
--
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:
> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you

Data Processing Script Justs Stops

I have a problem with a stored procdure I wrote. This proc will
process 80 to 100 million every night, it aggregates, inserts into a
different table, and then deletes the original data. The process will
hang after processing 20 to 40 million records. I must stop the proc
and then restart it before I can complete the process.

Any ideas why it would hang?

Current Specs:
Hardware: Compaq Proliant 370 - Dual 933MHz - 1GB or Ram - 35 gig Raid
5
OS: Windows 2k SP4
DB: MS SQL 2000 Enterprise SP3Not without seeing the SP.
Maybe the transaction log is unable to grow?

Nigel Rivett
www.nigelrivett.net

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Data Partioning...Urgent help req.

hi,
i have a table with 5 million records which has been horizonatal partitioned
into 3 tables. Each of these tables are located on different logical drives
using filegroups in the same database on a single SQL Server Instance.
Each table structure is:
InvID int Identity,
InvDate datetime
InvNumber varchar(50)
InvID and InvDate together form the primary key for each of the tables.
InvDate is my partioning column...which partitions my data according to
years.
I have then created a view using a select * and union all clause which
selects the data from these tables.
When querying data selectively i.e. for a specific year using the partioned
view i get a performance gain of around 10 % over selection of data from a
non partioned view i.e. from a single table containing all the 5 million
records.
Is their someway in which i can get a better performance gain, as i feel 10%
gain is not much...some of the articles i have read claim that we can get a
performance gain of upto 35%.
I also do not get any performance gain when loading data or when firing a
select * i.e. selecting all 5 million records.
Can i get performace gain in these through the concept of horizontal
partitioning.
Plz help.
Regards,
Harman.Harman,
I don't see any value in partitioning the table with such small amount of
data. I am a bit surprised that you saw any performance gain. If you have
much larger amount of data, and you have multiple physical drives, you are
more likely to see the performance benefit especially when multiple
partition elements are hit. The administrative overhead far far outstrip
any (if any) performance improvement even if you want the fastest response
with such small amount of data.
Better solution in your case would be using a clustered covering index --
here basically putting all the three columns into the index.
hth
Quentin
"Harman Dhillon" <harmand@.grapecity.com> wrote in message
news:#d5wIoVcDHA.2632@.TK2MSFTNGP12.phx.gbl...
> hi,
> i have a table with 5 million records which has been horizonatal
partitioned
> into 3 tables. Each of these tables are located on different logical
drives
> using filegroups in the same database on a single SQL Server Instance.
> Each table structure is:
> InvID int Identity,
> InvDate datetime
> InvNumber varchar(50)
> InvID and InvDate together form the primary key for each of the tables.
> InvDate is my partioning column...which partitions my data according to
> years.
> I have then created a view using a select * and union all clause which
> selects the data from these tables.
> When querying data selectively i.e. for a specific year using the
partioned
> view i get a performance gain of around 10 % over selection of data from a
> non partioned view i.e. from a single table containing all the 5 million
> records.
> Is their someway in which i can get a better performance gain, as i feel
10%
> gain is not much...some of the articles i have read claim that we can get
a
> performance gain of upto 35%.
>
> I also do not get any performance gain when loading data or when firing a
> select * i.e. selecting all 5 million records.
> Can i get performace gain in these through the concept of horizontal
> partitioning.
> Plz help.
> Regards,
> Harman.
>
>
>
>