SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Insert without knowing foreignkey id ahead of time
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 05/07/2009 :  12:15:46  Show Profile  Reply with Quote
quote:
Originally posted by Peso

quote:
Originally posted by whitefang

Since you guys have no real enterprise experience, I can tell you that the n-tier approach works faster.
What? He only wants to store the catalog and filename.
Do a DIR command in a CMD window will give you the same result, by storing each line to the procedure. With the "ugly hack" above this is doable quite fast and you don't have to worry about which order the catalogs arrive.

The point is to NOT have to call the database TWICE for storing ONE file.

So other than that, how's spring in Moscow?


E 12°55'05.63"
N 56°04'39.26"




You did not comprehend my previous post, probably cause you don't understand all that n-tier stuff.

Let me sum it up:
The n-tier procedure builds a list of file locations and inserts it into the filelocation table AS A SET. Then it builds a list of files with the filelocationid and then inserts it into the file table AS A SET.
In just two database calls, you're done (all your data is inserted).

Your caveman procedure technique involves the OP reading each file sequentially (this is what the OP is doing) and then sending it to a stored procedure for each file which in turn checks if the filelocation exists, and inserts if it doesn't. You're doing a SEQUENTIAL operation for something that SHOULD BE SET BASED.



I'll leave it at that with a Joe Celko quote:
"What is the best kind of rocks to smash screws into fine furniture?"
That is what the OP is asking, and you all just recommended that he uses granite.


Edited by - whitefang on 05/07/2009 13:15:18
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 05/07/2009 :  13:17:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Ok, prove your case by posting complete code for class(es) to retrieve filenames and store them in the database.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 05/07/2009 :  13:23:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Meanwhile, I created 25,000 random directories and 300,000 files of 4k each.

It took less than 20 seconds to store the names of all files into the database.
And add 1 minute to write the procedure above, and 2 minutes to write the procedure to get all filenames, give or take, so 3 minutes for complete solution! Not bad.

How are things going for you, WhiteFangm with intellisense and all?



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 05/07/2009 :  13:42:32  Show Profile  Reply with Quote
You got a database reading files now? Maybe it will cure cancer too. The definition of a database is lost.
Your solution is nothing more than a hack.




Edited by - whitefang on 05/07/2009 14:18:15
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 05/07/2009 :  13:51:15  Show Profile  Reply with Quote
quote:
Originally posted by Peso

And add 1 minute to write the procedure above, and 2 minutes to write the procedure to get all filenames, give or take, so 3 minutes for complete solution! Not bad.


E 12°55'05.63"
N 56°04'39.26"




Joe Celko:
SQL is not computational, so I would say that the database ought to
hold all rules relating to data integrity logic -- CHECK(), DEFAULT,
TRIGGER, and some Stored Procedures. Rules involving calculations or
process should be in a host application program, and especially
anything that involves an output like e-mail, hardcopy, etc. that
leaves the system.


Again, as I've been saying always, business logic does not belong in the database. Data integrity logic in the form of constraints is different from business logic, something you failed to realize.



Edited by - whitefang on 05/07/2009 13:52:24
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 05/07/2009 :  14:00:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You are sending a 24 megabyte string to the database?
And return 300,000 resultsets ("select @var1 as var1; ... ") from one call?

Not to offend you, but have you measured the memory consumption?


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 05/07/2009 14:04:43
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 05/07/2009 :  14:06:12  Show Profile  Reply with Quote
I'm sending a 24mb string to the DB.
You're trying to read a list of 25,000 files using the network on your DB.

What is your point? I send the 24mb string at once. You will encounter massive latencies because you're reading each file sequentially.

And if you're code runs in 20 seconds, mine would take maybe 2 seconds. .NET builds a list of 25,000 files in about half a second. That's without threading and parrell processing. Your time sounds right since the DB is not optimized for reading files over a network.

Memory consumption for 25,000 file objects is about 40mb which would be MASSIVE if you were in the 1950s.


It doesn't return 300,000 resultsets. It would actually be a concatenated string with a delimiter which the application would transform into ids.

Edited by - whitefang on 05/07/2009 14:10:08
Go to Top of Page

RyanAustin
Yak Posting Veteran

Canada
50 Posts

Posted - 05/07/2009 :  14:23:56  Show Profile  Click to see RyanAustin's MSN Messenger address  Reply with Quote
Out of curiousity, has anyone ever looked up the term "Plonker"

http://en.wikipedia.org/wiki/Plonker

I know its not a solution to the OP but, maybe some insight to the inane answer about n-tier??
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/07/2009 :  14:24:24  Show Profile  Reply with Quote
Good, the OP now understands how to build his enterprise level n-tier CD collection.



CODO ERGO SUM
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 05/07/2009 :  14:38:18  Show Profile  Reply with Quote
I just took this info and immediately implemented it in to our entire company where I was promoted to VP of Technology Who is not Wrong!

Jim
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 05/07/2009 :  14:43:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by whitefang

I'm sending a 24mb string to the DB.
How long will that take to parse?
quote:
Originally posted by whitefang

You're trying to read a list of 25,000 files using the network on your DB.
So you will because you will have to use the FindNextFile API call.
quote:
Originally posted by whitefang

You will encounter massive latencies because you're reading each file sequentially.
How will you read the filenames?
quote:
Originally posted by whitefang

And if you're code runs in 20 seconds, mine would take maybe 2 seconds. .NET builds a list of 25,000 files in about half a second. That's without threading and parrell processing. Your time sounds right since the DB is not optimized for reading files over a network.
What time do you think it will take to read all filenames for all catalogs with your class enumeration?
quote:
Originally posted by whitefang

Memory consumption for 25,000 file objects is about 40mb which would be MASSIVE if you were in the 1950s.
It will require more than 40 mb to hold 300,000 open resultsets.
quote:
Originally posted by whitefang

It doesn't return 300,000 resultsets. It would actually be a concatenated string with a delimiter which the application would transform into ids.
Yes you do.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 05/07/2009 :  14:51:32  Show Profile  Reply with Quote
quote:
Originally posted by Peso
So you will because you will have to use the FindNextFile API call.



Nice try but I'll be reading on the application side while you'll be reading over the network. I can create multiple threads, you cannot.


quote:
Originally posted by Peso
How will you read the filenames?



There would be multiple threads working in batches (there's really no need since .NET can read 25,000 in half a second).

quote:
Originally posted by Peso
What time do you think it will take to read all filenames for all catalogs with your class enumeration?



I told you, half a second. Have you ever build an n-tier app or done qa? One of the unit tests of an n-tier app is to call a standard "create" object method thousands of times.

quote:
Originally posted by Peso
It will require more than 40 mb to hold 300,000 open resultsets.



Nice try, but wrong as always. I said that the "ids" would be concatenated to a single column that would be parsed by the app layer in half a second. There is only 1 resultset (the code above is not optimized).


quote:
Originally posted by PesoYes you do.



I put that together in 2 minutes, it's not optimized. It should be concatenated into a single resultset.

Edited by - whitefang on 05/07/2009 14:52:08
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 05/07/2009 :  15:07:14  Show Profile  Reply with Quote
Show us your script so I can test it to make sure it runs under 20 seconds for 300,000 files in 25,000 catalogs because that is the same standard you're using to compare my solution.
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 05/07/2009 :  15:11:48  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Ok, prove your case by posting complete code for class(es) to retrieve filenames and store them in the database.



E 12°55'05.63"
N 56°04'39.26"




WF - we have yet to see your code either....

Terry

-- Procrastinate now!
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.36 seconds. Powered By: Snitz Forums 2000