New Posts New Posts RSS Feed: Fetching data from View -very slow when fetched from ideablade entity service
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Fetching data from View -very slow when fetched from ideablade entity service
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
Inder_gujral View Drop Down
Newbie
Newbie


Joined: 12-Jun-2011
Posts: 6 		Post Options Post Options   Quote Inder_gujral Quote  Post ReplyReply Direct Link To This Post Topic: Fetching data from View -very slow when fetched from ideablade entity service
    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
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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 here for our recommendations on slow query performance.

Hope this helps.
Back to Top
Inder_gujral View Drop Down
Newbie
Newbie


Joined: 12-Jun-2011
Posts: 6 		Post Options Post Options   Quote Inder_gujral Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down