|
Hi guys,
I've got a smart client application that allows the user to change, create, or delete data that's stored in an MSDE SQL database. I've rolled my own messaging queue so that the when a client updates lots of data at a time (e.g. deletes 500 rows of data, create 2 rows, update 100 rows), I send a batch message to my server application containing 602 sub-messages describing what happened (in other words, one big message containing all the actual messages describing what happened). This way I only have to go over the network from client to server only 1 time.
All that works great.
My problem is that now that the "delete 500, create 2, update 100" batch message is on the server, I have to call a stored procedure in the database for each message: call RemoveRow stored proc 500 times, call CreateRow 2 times, call UpdateRow 100 times.
Using the ANTS .NET profiler to look at my code, it is apparent that this execution of several hundred stored proc calls is a bottleneck. Is there any way to allow the MSDE database to execute a batch of commands all at once? Or better yet, is there a better way of doing this?
Tech, life, family, faith: Give me a visit.
I'm currently blogging about: Conversation With a Muslim
Judah Himango
|
|
|
|
|
Hi all
A database question.
Say for example I have a Suppliers table and a Products table in my database. If theoretically each supplier can supply millions of products what would be the most efficient and convenient?
Having a different Products table for each supplier, or a single Products table holding products of all suppliers. If the first option is better would it not be very tricky querying the data for reports and so on?
Thanks again.
Kobus
|
|
|
|
|
kbalias wrote:
what would be the most efficient and convenient?
That depends on how you are going to use the data. You can split the data by supplier and have a partitioned view to stich it back as if it were one big table - you can then query through the view.
Personally, I would keep it as one supplier table and one products table unless you run into performance problems then do work on eliminating the performance issues. I find that second guessing SQL Server on performance is often counter productive because the usage patterns are often not what was originally envisaged.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I have a column with type datetime in an employee's table. I want to delete the records that older than 3 working days. The problem is I can't use getdate()-3 in where clause because I don't know about the holidays and all.. Can anyone help me out ?
Regards,
Zishan
|
|
|
|
|
|
Can't I use some kind of loop in the SQL 
|
|
|
|
|
|
Assuming you have a table called Holidays with the a column containing the date of each holiday you could write a query like this:
DECLARE @days int;
SELECT @days = COUNT(*)+3 FROM Holidays
WHERE HolidayDate <= getdate() AND HolidayDate >= getdate()-3
You can then use the @days variable to count back the number of days. For example:
DELETE FROM MyTable WHERE somedate < getdate-@days
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Well.. you're right couldn't do it in SQL without a holidays table. However I did it in my C# program. I selected distinct dates from table ordered by Date Desc. Then I deleted every record with the date from my select query ignoring the first three..  Yes I know its poor performance, but this chunk has to run once every morning so I don't need any major performance here. And I had to do it without A holidays table. Thanks for your help 
Regards
Zishan
|
|
|
|
|
1. How to look witch tables are in the database.
2. Is possible to create database with my application. And How?
|
|
|
|
|
I work on several programs that require ordinary users to be able to change settings system wide (specific to the programs). However, Windows security is evolving toward forbiding any of the obvious aproaches to sharing data in this way (such as HKEY_LOCAL_MACHINE, and files in the same directory that the program is installed in) The simplest solution that I've been able to find is to use an MSDE database to store shared settings. However, a database server intended to be used over a network seems to be overkill to me. Does anyone have some ideas for my problem?
Nathan Holt
|
|
|
|
|
MSDE is actually limited in how many network connections it can take, however, it sounds like Access would do what you want. I have deployed MSDE for use on single machines, but I was building a database that Access just wasn't good enough for.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Christian Graus wrote:
MSDE is actually limited in how many network connections it can take, however, it sounds like Access would do what you want. I have deployed MSDE for use on single machines, but I was building a database that Access just wasn't good enough for.
Thanks. I finally found info yesterday that one is allowed to have shared files in the All Users/Application Data provided that one makes a subdirectory and sets its permisions so that all users can write to them. That makes most of the code I have easily fixable.
Nathan Holt
|
|
|
|
|
Are the effects of DDL events like droping or altering an object stored anywhere in SQL Server 2000? For example, if a stored procedure is altered is there some way of getting this information. The SQL server logs only seem to contain info on database level events (start, stop, backup, dbcc check, etc). Is there something similar that would allow me to find the date modified of a table or when a stored procedure was dropped?
|
|
|
|
|
Hello,
I'm having a little problem with the COUNT function. I have a table with payments for customers (say 42 or 54 each), and each payment is marked as paid whenever the customer comes and pays it.
I want to make a query that gives me all the customers with the number of paid payments (including those customers new for this week). The total number of customers is 328, but if I add the COUNT(*) the resulting table only contains 291, all with 1 or more payments (meaning 37 customers have 0 payments.)
Looking in the web I found that COUNT(column) returns the number of rows in which that column is not NULL. In this case, I'm using COUNT(*) which should return the count of all rows. I'm also using a WHERE clause to limit the counted payments to only those that have been paid. I tried a LEFT JOIN, so that at least the resultset would include the rows, even if the count column is NULL, but it didn't work.
Any ideas?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
|
Not exactly the answer I needed, but that'll help too.
I think my problem was that I was counting all the records from a resultset matching a criteria instead of counting some records matching a criteria from resultset from all records. I split the query into embedded subqueries and the results came as expected, except I was getting NULL instead of zeros. Your suggestion fixed it.
Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
do you group by the right columns ?
how is your select written please ?
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
|
Hi,
Is there a reason why sometimes some of the link options in Crystal Reports are disabled?
eg the FULL OUTER JOIN.
I can't think of a reason why a full outer join wouldn't work?
Hope somebody can help me.
tnx!
No hurries, no worries.
|
|
|
|
|
Hi,
I found that there are two practices for writing queries for adapter.
1. one adapter queries for one table in the database
e.g. select * from customer
2. one adapter queries for joinned table in the database
e.g. select * from customer c1, country c2 where c1.country = c2.country
Are the update/insert/delete command of an adapter work only for one table only?
Which practice is encouraged?
If the two tables are filled into the dataset, are there any SQL queries method to do the "join" operation to the two tables? anymore operations can be done?
Thanks
|
|
|
|
|
scchan1984 wrote:
Are the update/insert/delete command of an adapter work only for one table only?
Personally, I would never rely on the auto generated code for the INSERT/UPDATE/DELETE commands in an Adapter - because it cannot assume anything about the database it creates some awful SQL to ensure that it works.
scchan1984 wrote:
Which practice is encouraged?
I would encourage you not to use any Wizard generated adapters. Also, never do a SELECT * if the underlying table changes then the results back to your application may be different and your application may not expect that.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Is there a way to package and deploy only the engine and any runtime support for a SQL Server database?
If not and I have to tell the user that SQL Server is required, what is the easiest way to install my application's schema and data into an existing SQL Server database? In previous situations, I've exported the data and used the bcp command-line app to import the data. Is that the best way or is there an alternative?
Cheers,
Tom Archer - Archer Consulting Group
"So look up ahead at times to come, despair is not for us. We have a world and more to see, while this remains behind." - James N. Rowe
|
|
|
|
|
|
I have a DataTable (a table in ASP.NET) in memory which I need to pass into an SQL stored procedure.
How can I achieve this? Is there any to pass the contents of the table into my stored procedure given that the size (number of rows) of the table is unknown?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
