Print Page | Close Window

Fetching data from View -very slow when fetched from ideablade entity service

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=3312
Printed Date: 13-May-2026 at 9:40am


Topic: Fetching data from View -very slow when fetched from ideablade entity service
Posted By: Inder_gujral
Subject: Fetching data from View -very slow when fetched from ideablade entity service
Date Posted: 01-Mar-2012 at 1:13pm
Hi,

I am  using Silverlight client and I have a MySql Backend.

I have created some views and on the UI, I am trying to fetch the data from the view by applying a filter on the view.

The query performance is very poor and I am receiving timeouts. When I run the query on database, it works very fast , under 1 sec it returns the results.

But on the UI, it times out.

I checked the mysql.slowlogs and found out that this query is being generated for the view- 'company_item_data'

SELECT
`Extent1`.`ArtistName`,
`Extent1`.`artprice`,
`Extent1`.`art_cost`,
`Extent1`.`Category`,
`Extent1`.`DateReceived`,
`Extent1`.`EditionName`,
`Extent1`.`idcompany`,
`Extent1`.`idcompany_item`,
`Extent1`.`isconsigned`,
`Extent1`.`LocationName`,
`Extent1`.`Medium`,
`Extent1`.`qoh`,
`Extent1`.`scancode`,
`Extent1`.`subject`,
`Extent1`.`title`,
`Extent1`.`TransCount`,
`Extent1`.`updated_by`,
`Extent1`.`VendorName`
FROM (SELECT
      `company_item_data`.`ArtistName`,
      `company_item_data`.`artprice`,
      `company_item_data`.`art_cost`,
      `company_item_data`.`Category`,
      `company_item_data`.`DateReceived`,
      `company_item_data`.`EditionName`,
      `company_item_data`.`idcompany`,
      `company_item_data`.`idcompany_item`,
      `company_item_data`.`isconsigned`,
      `company_item_data`.`LocationName`,
      `company_item_data`.`Medium`,
      `company_item_data`.`qoh`,
      `company_item_data`.`scancode`,
      `company_item_data`.`subject`,
      `company_item_data`.`title`,
      `company_item_data`.`TransCount`,
      `company_item_data`.`updated_by`,
      `company_item_data`.`VendorName`
      FROM `company_item_data` AS `company_item_data`) AS `Extent1`
 WHERE 18 = `Extent1`.`idcompany`
 ORDER BY
`Extent1`.`idcompany_item` ASC LIMIT 25

Ideally, only the subquery should be generated with the filters , ex:
SELECT
      `company_item_data`.`ArtistName`,
      `company_item_data`.`artprice`,
      `company_item_data`.`art_cost`,
      `company_item_data`.`Category`,
      `company_item_data`.`DateReceived`,
      `company_item_data`.`EditionName`,
      `company_item_data`.`idcompany`,
      `company_item_data`.`idcompany_item`,
      `company_item_data`.`isconsigned`,
      `company_item_data`.`LocationName`,
      `company_item_data`.`Medium`,
      `company_item_data`.`qoh`,
      `company_item_data`.`scancode`,
      `company_item_data`.`subject`,
      `company_item_data`.`title`,
      `company_item_data`.`TransCount`,
      `company_item_data`.`updated_by`,
      `company_item_data`.`VendorName`
      FROM `company_item_data` AS `company_item_data`
WHERE 18 = `company_item_data`.`idcompany`
 ORDER BY
`company_item_data`.`idcompany_item` ASC LIMIT 25

Because of the subquery the query performance is considerably poor. Can you please tell me what am I doing wrong here ?
or what is causing the subquery generation instead of the second query?


Thanks


Replies:
Posted By: DenisK
Date Posted: 02-Mar-2012 at 5:08pm
Hi Inder_gujral,

It's unclear whether it is DevForce or EntityFramework that is slowing the query down. DevForce always passes the query over to EntityFramework to be executed.

I would do the following tests first to determine exactly where the query is slowing down.

1. In an isolated unit test, separate from the UI, run the view's query.

2. Do the same thing, except this time, use raw EntityFramework instead of DevForce.

3. If DevForce is slower, see the DebugLog.xml that gets generated for any clues.

You can also see http://drc.ideablade.com/xwiki/bin/view/Documentation/query-performance - here for our recommendations on slow query performance.

Hope this helps.

Posted By: Inder_gujral
Date Posted: 03-Mar-2012 at 1:25am
Hello Denis

I think the more specific question is why the SQL being sent to the database is being formed the way it is.

I have a database view "company_item_data" stored in mysql database. So, when I run a query on that view through Ideablade, the query being sent to the database should have been:

SELECT
      `company_item_data`.`ArtistName`, 
      `company_item_data`.`artprice`, 
      `company_item_data`.`art_cost`, 
      `company_item_data`.`Category`, 
      `company_item_data`.`DateReceived`, 
      `company_item_data`.`EditionName`, 
      `company_item_data`.`idcompany`, 
      `company_item_data`.`idcompany_item`, 
      `company_item_data`.`isconsigned`, 
      `company_item_data`.`LocationName`, 
      `company_item_data`.`Medium`, 
      `company_item_data`.`qoh`, 
      `company_item_data`.`scancode`, 
      `company_item_data`.`subject`, 
      `company_item_data`.`title`, 
      `company_item_data`.`TransCount`, 
      `company_item_data`.`updated_by`, 
      `company_item_data`.`VendorName`
      FROM `company_item_data` AS `company_item_data`


but instead what is being sent is:

SELECT
`Extent1`.`ArtistName`, 
`Extent1`.`artprice`, 
`Extent1`.`art_cost`, 
`Extent1`.`Category`, 
`Extent1`.`DateReceived`, 
`Extent1`.`EditionName`, 
`Extent1`.`idcompany`, 
`Extent1`.`idcompany_item`, 
`Extent1`.`isconsigned`, 
`Extent1`.`LocationName`, 
`Extent1`.`Medium`, 
`Extent1`.`qoh`, 
`Extent1`.`scancode`, 
`Extent1`.`subject`, 
`Extent1`.`title`, 
`Extent1`.`TransCount`, 
`Extent1`.`updated_by`, 
`Extent1`.`VendorName`
FROM (SELECT
      `company_item_data`.`ArtistName`, 
      `company_item_data`.`artprice`, 
      `company_item_data`.`art_cost`, 
      `company_item_data`.`Category`, 
      `company_item_data`.`DateReceived`, 
      `company_item_data`.`EditionName`, 
      `company_item_data`.`idcompany`, 
      `company_item_data`.`idcompany_item`, 
      `company_item_data`.`isconsigned`, 
      `company_item_data`.`LocationName`, 
      `company_item_data`.`Medium`, 
      `company_item_data`.`qoh`, 
      `company_item_data`.`scancode`, 
      `company_item_data`.`subject`, 
      `company_item_data`.`title`, 
      `company_item_data`.`TransCount`, 
      `company_item_data`.`updated_by`, 
      `company_item_data`.`VendorName`
      FROM `company_item_data` AS `company_item_data`) AS `Extent1`
 WHERE 18 = `Extent1`.`idcompany`
 ORDER BY 
`Extent1`.`idcompany_item` ASC LIMIT 25

Optimization tips for Ideablade would probably come in to picture if it were slow even when the query being passed to the database is accurate.

My question is really not about performance optimization - my question is about inappropriate formation of query being passed over to the database.

Please let me know why that is the case.

Posted By: DenisK
Date Posted: 05-Mar-2012 at 8:10pm
Hmm, without seeing some code it's hard to determine what's going on. I can help better if you can zip up a sample test showing the client query and a sql script to create the database view along with the involved tables. You can send me a PM if you prefer.


Print Page | Close Window