Run too slow

[Raymond Chiu]

Dear All,

I have the following C# code which has one line run very slow when I debug.
I have added the index (default_month and mebr_id) to the fund_choice table
in the sql server 2008 database.
But still no use. Do you know where is the problem???
-------------------------------------------------------------------------------------------------------------------------
IEnumerable<Model.DBML.FUND_CHOICE> FundChoicedto
= from F in fundchoice
where F.Default_Month == '200902' && F.Mebr_Id == fundDto.Mebr_Id
select F;

if (FundChoicedto.Count() > 0) /// It is very slow to run this command

 

[Raymond Chiu]

I just withdraw one record everytime within the loop.
There are totally 600 records that need to process.
But don't know why when withdraw one record within the loop , it need to
wait for a long time.

By doing that you're going to iterate through the resultset returned by
your query, so if you have a million rows you need to wait until it
finishes. It doesn't matter if you have
an index, at the end you'll be performing a table scan (from the top to
the bottom).


Regards,


--
Angel J. Hernández M
MCP,MCAD,MCSD,MCDBA
Microsoft MVP
http://msmvps.com/blogs/angelhernandez
***************************************************
Technical Solution Architect
http://www.customware.net

Dear All,

I have the following C# code which has one line run very slow when I
debug. I have added the index (default_month and mebr_id) to the
fund_choice table in the sql server 2008 database.
But still no use. Do you know where is the problem???
-------------------------------------------------------------------------------------------------------------------------
IEnumerable<Model.DBML.FUND_CHOICE> FundChoicedto
= from F in fundchoice
where F.Default_Month == '200902' && F.Mebr_Id == fundDto.Mebr_Id
select F;

if (FundChoicedto.Count() > 0) /// It is very slow to run this command

 

[Michael C]

I just withdraw one record everytime within the loop.
There are totally 600 records that need to process.
But don't know why when withdraw one record within the loop , it need to
wait for a long time.

Try if (FundChoicedto.Any())

By doing that you're going to iterate through the resultset returned by
your query, so if you have a million rows you need to wait until it
finishes. It doesn't matter if you have
an index, at the end you'll be performing a table scan (from the top to
the bottom).


Regards,


--
Angel J. Hernández M
MCP,MCAD,MCSD,MCDBA
Microsoft MVP
http://msmvps.com/blogs/angelhernandez
***************************************************
Technical Solution Architect
http://www.customware.net

Dear All,

I have the following C# code which has one line run very slow when I
debug. I have added the index (default_month and mebr_id) to the
fund_choice table in the sql server 2008 database.
But still no use. Do you know where is the problem???
-------------------------------------------------------------------------------------------------------------------------
IEnumerable<Model.DBML.FUND_CHOICE> FundChoicedto
= from F in fundchoice
where F.Default_Month == '200902' && F.Mebr_Id == fundDto.Mebr_Id
select F;

if (FundChoicedto.Count() > 0) /// It is very slow to run this command

 

[Raymond Chiu]

We find that if we use executequery in the coding with traditional sql
string ( select * from xxx) is very fast within one second.

Is Linq features not so good?

I just withdraw one record everytime within the loop.
There are totally 600 records that need to process.
But don't know why when withdraw one record within the loop , it need to
wait for a long time.

Try if (FundChoicedto.Any())

By doing that you're going to iterate through the resultset returned by
your query, so if you have a million rows you need to wait until it
finishes. It doesn't matter if you have
an index, at the end you'll be performing a table scan (from the top to
the bottom).


Regards,


--
Angel J. Hernández M
MCP,MCAD,MCSD,MCDBA
Microsoft MVP
http://msmvps.com/blogs/angelhernandez
***************************************************
Technical Solution Architect
http://www.customware.net



Dear All,

I have the following C# code which has one line run very slow when I
debug. I have added the index (default_month and mebr_id) to the
fund_choice table in the sql server 2008 database.
But still no use. Do you know where is the problem???
-------------------------------------------------------------------------------------------------------------------------
IEnumerable<Model.DBML.FUND_CHOICE> FundChoicedto
= from F in fundchoice
where F.Default_Month == '200902' && F.Mebr_Id == fundDto.Mebr_Id
select F;

if (FundChoicedto.Count() > 0) /// It is very slow to run this command

 

[Raymond Chiu]

of course, before we put
IEnumerable<Model.DBML.FUND_CHOICE> fundchoice =
dcx.GetTable<Model.DBML.FUND_CHOICE>();
which seems to run the whole table? is it true??
IEnumerable<Model.DBML.FUND_CHOICE> FundChoicedto
= from F in fundchoice
where F.Default_Month == '200902' && F.Mebr_Id == fundDto.Mebr_Id
select F;

if (FundChoicedto.Count() > 0) /// It is very slow to run this command

I just withdraw one record everytime within the loop.
There are totally 600 records that need to process.
But don't know why when withdraw one record within the loop , it need to
wait for a long time.

Try if (FundChoicedto.Any())

By doing that you're going to iterate through the resultset returned by
your query, so if you have a million rows you need to wait until it
finishes. It doesn't matter if you have
an index, at the end you'll be performing a table scan (from the top to
the bottom).


Regards,


--
Angel J. Hernández M
MCP,MCAD,MCSD,MCDBA
Microsoft MVP
http://msmvps.com/blogs/angelhernandez
***************************************************
Technical Solution Architect
http://www.customware.net



Dear All,

I have the following C# code which has one line run very slow when I
debug. I have added the index (default_month and mebr_id) to the
fund_choice table in the sql server 2008 database.
But still no use. Do you know where is the problem???
-------------------------------------------------------------------------------------------------------------------------
IEnumerable<Model.DBML.FUND_CHOICE> FundChoicedto
= from F in fundchoice
where F.Default_Month == '200902' && F.Mebr_Id == fundDto.Mebr_Id
select F;

if (FundChoicedto.Count() > 0) /// It is very slow to run this command

 

[Michael C]

We find that if we use executequery in the coding with traditional sql
string ( select * from xxx) is very fast within one second.

Is Linq features not so good?

My opinion is that you're better off writing sql using sql.

Michael

 

[Pavel Minaev]

IEnumerable<Model.DBML.FUND_CHOICE> FundChoicedto
= from F in fundchoice
where F.Default_Month == '200902' && F.Mebr_Id == fundDto.Mebr_Id
select F;

if (FundChoicedto.Count() > 0) /// It is very slow to run this command

The problem here is that you save the result of your query to
IEnumerable. At that point, any LINQ operators you apply to it cannot
"optimize for database" - they will process your result-set as if it
was an in-memory collection. Specifically. Enumerable.Count() that
you're using here is going to iterate through all the records just so
that it can count them. Of course that is going to be slow!

If instead, you had written:

IQueryable<Model.DBML.FUND_CHOICE> FundChoicedto = ...

then all LINQ operations on that IQueryable would be database-aware.
Specifically, your Count() call would be translated to SELECT COUNT(),
and so on. Better yet, use Any() as advised above - this is even more
efficient.

 

[Paul]

If you need perfromance do not use Linq or datasets.

Create custom objects and use a Data Access Layer for populating these
objects. It is time tested and very fast.

Linq is useful for knocking apps up quick but do not expect performance. TBH
tho once you have created your own DAL/Data Provider/Model Base framework,
you will be able to create a DAL for a new app quicker than using LINQ.

Thats my opinion anyway.

IEnumerable<Model.DBML.FUND_CHOICE> FundChoicedto
= from F in fundchoice
where F.Default_Month == '200902' && F.Mebr_Id == fundDto.Mebr_Id
select F;

if (FundChoicedto.Count() > 0) /// It is very slow to run this command

The problem here is that you save the result of your query to
IEnumerable. At that point, any LINQ operators you apply to it cannot
"optimize for database" - they will process your result-set as if it
was an in-memory collection. Specifically. Enumerable.Count() that
you're using here is going to iterate through all the records just so
that it can count them. Of course that is going to be slow!

If instead, you had written:

IQueryable<Model.DBML.FUND_CHOICE> FundChoicedto = ...

then all LINQ operations on that IQueryable would be database-aware.
Specifically, your Count() call would be translated to SELECT COUNT(),
and so on. Better yet, use Any() as advised above - this is even more
efficient.