| Author |
 Topic  |
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
 Posted - 05/07/2009 : 12:15:46
|
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 |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/07/2009 : 13:17:02
|
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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/07/2009 : 13:23:53
|
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" |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 05/07/2009 : 13:42:32
|
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 |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 05/07/2009 : 13:51:15
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/07/2009 : 14:00:34
|
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 |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 05/07/2009 : 14:06:12
|
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 |
|
|
|
RyanAustin
Yak Posting Veteran
Canada
50 Posts |
Posted - 05/07/2009 : 14:23:56
|
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?? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 05/07/2009 : 14:24:24
|
Good, the OP now understands how to build his enterprise level n-tier CD collection.
CODO ERGO SUM |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 05/07/2009 : 14:38:18
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/07/2009 : 14:43:06
|
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" |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 05/07/2009 : 14:51:32
|
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 |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 05/07/2009 : 15:07:14
|
| 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. |
|
|
|
tosscrosby
Aged Yak Warrior
USA
676 Posts |
Posted - 05/07/2009 : 15:11:48
|
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! |
|
|
|
Topic |
|
Insert without knowing foreignkey id ahead of time
