June 15, 2011 at 1:38 pm
I got a friendly nudge to come look at this thread and my first impression is this seems like a lot of work to work around a c# problem..
It sounds like your sproc exists to "page" the data out to the C# program so it can encrypt and zip it, is that about right?
How big is the end file? what kind of encryption are you looking to use? What compression method are you looking to use?
SSIS is an extremely effective method for getting data into and out of a server, loading millions of records is not very painful at all. Also, keep in mind you can access a lot of .net functionality within the dataflow..
CEWII
June 15, 2011 at 1:41 pm
Elliott Whitlow (6/15/2011)
I got a friendly nudge to come look at this thread and my first impression is this seems like a lot of work to work around a c# problem..
I don't know whatever you are talking about :hehe:.
Telekinesis maybe, I swear I never was in that room with you. :alien:
June 15, 2011 at 1:45 pm
BTW what sql version are you on? This can't be 7, 2000 since you have a row_number() in there.
June 15, 2011 at 1:59 pm
Well since we are talking about CTE's we are talking about at least what, 2005?
can't remember what version they were introduced in..
CEWII
June 15, 2011 at 2:00 pm
Elliott Whitlow (6/15/2011)
Well since we are talking about CTE's we are talking about at least what, 2005?can't remember what version they were introduced in..
CEWII
2k5, same as row_number()
June 15, 2011 at 3:17 pm
Okay. Thank you everyone for your replies. 🙂
You're right, if it was smooth sailing then I wouldn't be here.
I got it to preform well. My problem was the query I was using and with that article I found, the section in there not using the temp table but the last chunk of code,
works great for me and now time is consistent and good across the board.
I also learned a lot about parameter sniffing while trying to solve this and used some of the stupid work arounds for that which also helped.
Thanks again everyone! 🙂
June 15, 2011 at 3:36 pm
I have used SSIS for migrating from Oracle to a new structure in sql server. Like it very much but for this, C# just makes sense.
I can provide a nice user interface (which I also did when I wrote the upgrade in SSIS that C# called).
Provide a lot of options for the user as to what type of data they want to archive off, import in etc.
I can create the user interface I want for archiving and then bringing back the data into a table from flat files.
The use of datatables in C# is very cool. User provides parameters etc that I can use to query the data from the flat files so that I can just suck in the data they're interested in.
June 15, 2011 at 11:29 pm
aracy (6/15/2011)
Okay. Thank you everyone for your replies. 🙂You're right, if it was smooth sailing then I wouldn't be here.
I got it to preform well. My problem was the query I was using and with that article I found, the section in there not using the temp table but the last chunk of code,
works great for me and now time is consistent and good across the board.
I also learned a lot about parameter sniffing while trying to solve this and used some of the stupid work arounds for that which also helped.
Thanks again everyone! 🙂
Could you post your solution, please? Paging is a huge pain for most folks and a good performing solution would help a lot of people... thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply