|
Hi, Thanks for your answer, i have tried the (WITH NOLOCK) on the select query but it still takes time to insert the new rows when running the select. it will wait until the select finishes.
|
|
|
|
|
Like I stated the nolock is a hint given to the database, and the database server might choose to ignore it. From what I know, for at least for MS SQL, is that the nolock should solve the issue. But you could try to combine it with a ROWLOCK or PAGELOCK instruction for the insert statement, see locking hints for more on this.
Keep in mind that large sets of indexes could also cause the locking you are experiencing, as an insert will update the index and could trigger a re-index.
|
|
|
|
|
Is your SP speed acceptable? Or should it really execute much faster when done properly? Did you look into your indexing scheme?
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
The SP are fine and tabled are well indexed, just the search results are very complicated calculations running on millions of records , and the problem is that when running the select for client to see in application, no inserts are allowed.
|
|
|
|
|
I guess Gerben's answer applies then.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
I would certainly look into ways of speeding up the query. Perhaps copy the relavent data to another table and do the calculations there.
|
|
|
|
|
I agree that you might want to build a "reporting" table that is refreshed nightly, hourly, whatever and have the users query against that table. Sometimes the data doesn't need to be up to the minute ... just make sure the end user knows that the data may be somewhat stale.
I've used this method for a few reports and the first user who initiates the report takes the hit and creates the reporting view for the day. everyone request for the report goes against that dataset; each day a new dataset is created only if someone requests it. Each time a dataset is requested, all previous datasets are deleted.
Just a thought. ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Good luck
|
|
|
|
|
Revisit you code..... Are you using cursors? Also, it may be better to get the raw data into an intermediary table, then select from that table and perform computations, then insert into the destination table. Of course you should always clear the intermediate table before you start putting data into it.
|
|
|
|
|
Your SELECT query is locking the whole table that is forcing the database engine to dishonor exclusive lock requests by INSERT statements. This could happen if the query is fetching most of the rows in the tables.
1. Revisit your SELECT query and make sure your select only those rows/columns required.
2. Index your table(s).
|
|
|
|
|
Eli Nurman wrote: i cannot insert new rows into that table, how is this possible to do
How are you trying to update (insert) the table? By default SSIS for example trys to lock the table before doing any data changes, that behavior can be changed.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Can I make a derived table with a name for all
then an alias column names for each of the three unions
SELECT A.strName
FROM
(
SELECT strName
FROM [dbo].[Summary]
UNION
SELECT strName
FROM [dbo].[Summary_Old]
UNION
SELECT [TABLE_NAME]
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
) AS A
So it will look as follows:
strName, strName1, strName2, strName3, strCompare
All names will fall into strName
then only names in the first table will fall into strName1
etc.
Then I would like to compare these names in a fifth column strCompare
to add, delete, NA so I can synchronize tables and database
the Table Names are a reference for all tables in the DB to strNames
or should I set the names into a temp table then add columns and compare
How would I do the above either in derived or temp
Any help is appreciated
Thanks in advance,
Michael
|
|
|
|
|
I'm not sure about the fifth column but the strName1, strName2, strName3, can be made by using the great and magnificent left outer join operation.
|
|
|
|
|
what exactly is the problem? i.e. what are you trying to acomplish?
|
|
|
|
|
I am not new but somehow I have really avoided getting too involved in database design. My question is fairly basic. If I have a relationship where one object technically possesses many of another, should I just make a standard many to one relation or should I create a bridge table? I will give an example. Let's say that I am trying to keep track of credit card numbers and their owners which I am not just so I don't get screamed at by the security people. The real example would be too hard to explain but this is comparable. So anyways, should I have a credit card numbers table that will have ID (pri key), CCnumber, and Owner columns? Then I would point the Owner column at the pri key column in the People table right? Does it matter that technically that people own credit cards and then maybe I should be making a bridge table? My hunch is that I don't need a bridge table but since this is going to be a big project, I would rather not find out the hard way after I have over 100 tables.
|
|
|
|
|
Using your exampleof people and credit cards, you would have a one-to-many relationship. FYI, it is not called a many-to-one.
You would take the primay key in the people table and include it as a foreign key in the credit card table.
In my experience, you only need a bridge table when you have a many-to-many relationship. A many to many relationship is always broken down to two one-to-many relationships. Consider Students and Courses. A student may take 0,1 or many courses and a course may be taken by 0, 1 or several students. In this case you create an intermediary/relationship/bridge table in which the you have foreign keys from the student and course tables.
I would also advice that you start getting involved in database design
|
|
|
|
|
In your example, a Credit Card can be owned by one and only one person at a time, therefore a bridge table would not be required. A bridge table is required only in a many-to-many relationship.
|
|
|
|
|
I have multiple websites and i am planning to put all on the same web host. All will be storing data on mySQL databases.
My question.... if I want to use the same authentication details (user id and password) for all websites, which way you prefer:
1. Create the same member table in every database related to website, for example in websiteDB1, websiteDB2, etc? so every websiteDB will have membersDB? but technically all memberDB are the same because it's storing the same userIDs and passwords?
2. Create a separate database called memberDB and connect to it from website1 and website2 when the user wants to authenticate?
what's you suggestion?
Thanks,
Jassim
|
|
|
|
|
This depends a lot on the expectations of your users: do they know that the same user name and password let them log in to multiple web sites, or do they expect to be able to change their password on website#1, and keep the old password on website#2? If user name and password must be kept synchronized for all web sites, then there should be only one table; if each web site should have its own pair of user name and password, then obviously there should be multiple tables.
|
|
|
|
|
what about using a separate db for the users profiles but instead of having a single record for the user id and password I'll use multiple records with a flag indicating which service this user id and password belongs to?
|
|
|
|
|
It's a viable alternative, too, but given that you were planning on creating multiple databases anyway, this would seem like an extra maintenance headache. On the other hand, if your database would store little or no site-specific data (i.e. you're creating them almost specifically to support user logins), then using a single database with a flag in a table is definitely a better choice. A lot of this depends on your requirements: once you know what you are trying to achieve, doing the technical side becomes nearly mechanical 
|
|
|
|
|
actually it's different websites including personal site so i thought why should i keep all in one database and the scroll through the tables and stored procedures and ... and....
I am using mySQL and i thought it would be easier to have separate database for every website instead of putting all together or even create a schema because that might end up to large database size?
what do you think?
|
|
|
|
|
If you have different databases for different web sites, and your users would need separate logins to each web site, then keeping the login data with the rest of the site data sounds like a better choice from the design perspective.
|
|
|
|
|
Go with option 2, it sound horrible duplicating user profiles in each db. You could setup tables in your memberDB to control which app each user can login to.
|
|
|
|
|
I am trying to read table data from all my tables in a database to a summary table which contains the table anme in a column, but I am not sure what is the best way to go about doing this. Should I use temp tables or sp_executesql or some other method. I want to use a variable for the table name but I don't think that will work unless I split the string below in 2 and insert a table name. Or should I use temp tables which I am not familiar with or other method, if so how would I do that.
I appreciate any help I can get.
Thanks in advance,
Michael
SET @SQLString1 = N'
DECLARE
@dtLatestDate DATETIME
SELECT @dtLatestDate = B.dtDateTime
FROM
(
SELECT TOP (1) dtDateTime
FROM '
(split here the add tablename, see below)
@TableName
SET @SQLString2 = N'
ORDER BY dtDateTime DESC
) AS B'
OPEN strTableName_cursor;
FETCH NEXT FROM strTableName_cursor INTO @strTableName;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @SQLString1 + @TableName + @SQLString2
UPDATE [dbo].[Summary] SET dtLatestDate = @dtLatestDate WHERE strTableName = @strTableName
FETCH NEXT FROM strTableName_cursor INTO @strTableName
END
CLOSE strSymbol_cursor
modified on Saturday, May 14, 2011 3:50 PM
|
|
|
|
|
If you want to vary the table name dynamically then you must use dynamic SQL. There is no alternative.
Either sp_executesql or 'execute' can be used.
Are you sure it needs to be dynamic though?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
