Redirecting query results a la INSERT SELECT or SELECT INTO

Posts   
 
    
cebus2000
User
Posts: 29
Joined: 11-Jan-2006
# Posted on: 30-Jul-2007 22:09:42   

Hello again, gents! Here's my situation:

We have rather complex search requirements for our apps. One way we minimize performance headaches is buy running smaller chunks of the search in advance, caching the results in a table, and joing these 'temp' results in to later queries. The reason we store these temp results in the DB and not in a client-side cache is that there could easily be millions of rows coming back from the intermediate queries, and we can't clog up the network like that.

So, what I need to do is something like this: INSERT INTO MyScratchTable(field1, field2...) SELECT field1, field2... FROM SearchTable1 JOIN SomeOtherTable WHERE Field1='foo', Field2 > 10, etc etc

1) Is there a way to do this with the build-in Adapter functionality (something like a super-fancy INSERT FROM with predicates?) 2) If no, is it possible for me to alter the disposition of results, so they are dumped in a table instead of being returned to the client (as in FetchEntityCollection())?

Oh, and I'm on verion 2.0.0.61023 of the runtime libraries, using the Adapter templates.

Thanks much!

Jessynoo avatar
Jessynoo
Support Team
Posts: 296
Joined: 19-Aug-2004
# Posted on: 31-Jul-2007 00:30:17   

Hi,

I'm afraid there's no easy solution to doing that through entities, since the insert logic explicitely uses ADO.Net parameters.

I guess the easiest is to go for a stored procedure, which you can trigger from an adpater.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 31-Jul-2007 06:12:32   

I found this http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=9334 Didn't tested but if you want to try and let us know, would be nice.

David Elizondo | LLBLGen Support Team
Jessynoo avatar
Jessynoo
Support Team
Posts: 296
Joined: 19-Aug-2004
# Posted on: 31-Jul-2007 09:28:04   

Hi daelmo,

Seems that your end dot corrupted your link. (FIXED it wink -- Otis)

Anyway, you could indeed fetch a typedlist into an entitycollection thanks to a projector for instance, and save back the entitycollection, but I guess you're talking about a remote database then you don't want the data to leave the server in the first place.

I would definitely go for a sproc.

Still, it could be an idea to update the insert dqe logic so that the entity fields are parsed for replacements expressions and compareset predicates.

cebus2000
User
Posts: 29
Joined: 11-Jan-2006
# Posted on: 31-Jul-2007 12:53:21   

Thanks for the responses. Unfortunately, I can't use a stored proc, b/c the number of search parameters is highly variable, there's comparison operators, etc. And yes, the idea here is to keep the result set from leaving the remote SQL server (SQL2005, by the way.)

I am wondering if I could perhaps build the query using Entities, extract the query and parameters from the Adapter, and then use the parameterized query as the source for an INSERT? Something like:

sp_ExecuteSql ' INSERT INTO MyScratchTable(...) [insert Entity query here] ', [entity params]

Is it possible to extract the params and query like that?

Thanks!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 31-Jul-2007 17:56:26   

cebus2000 wrote:

Thanks for the responses. Unfortunately, I can't use a stored proc, b/c the number of search parameters is highly variable, there's comparison operators, etc. And yes, the idea here is to keep the result set from leaving the remote SQL server (SQL2005, by the way.)

I am wondering if I could perhaps build the query using Entities, extract the query and parameters from the Adapter, and then use the parameterized query as the source for an INSERT? Something like:

sp_ExecuteSql ' INSERT INTO MyScratchTable(...) [insert Entity query here] ', [entity params]

Is it possible to extract the params and query like that?

Thanks!

You can cheat simple_smile If the action you want to do is simply inserting a resultset into a table, you can create a derived class of DataAccessAdapter and override OnFetchEntityCollection. In there you'll get the query to execute in the form of a RetrievalQuery object.

You can alter that object's command text by inserting an INSERT INTO statement in front of it. simple_smile

Frans Bouma | Lead developer LLBLGen Pro
cebus2000
User
Posts: 29
Joined: 11-Jan-2006
# Posted on: 31-Jul-2007 18:36:34   

Brilliant! smile I'll give that a shot, thanks once again.