Last post Aug 13, 2013 09:22 AM by Illeris
Aug 08, 2013 07:57 AM|Sincuabator|LINK
I have a wcf dataservice named properties.svc with MySQL database. And I included one table tbl as well as one view vw.
This view is just a select statement from the table tbl.
Now I am making http calls to fetch data from both table and view.
http://localhost/properties.svc/tbl?$filter= fruit eq 'apple'&$top=1
http://localhost/properties.svc/vw?$filter= fruit eq 'apple'&$top=1
I have captured the queries running in mysql server. These queries are dynamically making by data service with the http requests (that is what I believe).
And the following are the queries.
For the first one(from tbl):
SELECT `Project1`.`C1`, `Project1`.`C2`, `Project1`.`C3`, `Project1`.`id`, `Project1`.`fruit` FROM (SELECT `Extent1`.`id`, `Extent1`.`fruit`, 1 AS `C1`, 'propmodel.tbl' AS `C2`, 'id,fruit' AS `C3` FROM `tbl` AS `Extent1` WHERE 'APPLE' = `Extent1`.`fruit`) AS `Project1` ORDER BY `Project1`.`property_id` ASC LIMIT 1
For the second one(from vw):
SELECT `Extent1`.`id`, `Extent1`.`fruit` FROM (SELECT `vw`.`id`, `vw`.`fruit` FROM `vw` AS `vw`) AS `Extent1` WHERE 'APPLE' = `Extent1`.`fruit` ORDER BY `Extent1`.`ID` ASC LIMIT 1
Look while fetching from table the filtering is applied with subquery, but while from view, filtering is applied to the complete result set of that table. This is just a demonstration. My tables are really big (6 GB and 80 lakhs records).
Actual view have joins as well. The problem is, it is throwing timeout exception, since trying to fetch all data at once.
How can I configure my WCF dataservice to make queries in such a way that filters are getting applied inside the subqueries while fetching data from views?
Please help me.
Aug 13, 2013 09:22 AM|Illeris|LINK
I do not think you can modify this behavior. An alternative would be to write your own dataservices.