|
I thought that I understood them, but they aren't working as I expected. Perhaps someone can help dispel my confusion?
I have a table that is comprised of an incrememnting, numeric primary key, a date field, and several other fields (that are irrelevant to the problem). It is range partitioned by day on the date field, thus creating a new table partition for every 24 hours of data.
The problem comes when I need to drop an old partition. I created the PK index as LOCAL, which (as I understood from the documentation) should have made it equipartition with the table. However, when the old partition(s) are dropped, it becomes unusable, and rebuilding the index can take hours (it is a VLDB).
Any ideas what I am doing wrong..? Thank you.
|
|
|
|
|
Found this answer from Ask Tom... It appears that I have no choice but to use a global index on the PK.
|
|
|
|
|
how to create a database that stores an images
mrjaya
|
|
|
|
|
Depends on your database server, there is plenty of information out there for SQL Server[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
My query
-- parameter of the procedure
@abc int = null;
declare @txt varchar(100);
set @txt = 'select * from tablename where field1 = isnull(@fld,field1)'
set @txt = replace(@txt,'@fld',@abc)
exec(@txt)
when @abc has any value then its work fine, when @abc is null then @txt show null
how can i concatenate this string and execute it
Thankyou
Ypki
|
|
|
|
|
Initialize variable @abc with 0 value, not with NULL.
e.g set @abc=0
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
modified on Wednesday, May 18, 2011 3:12 AM
|
|
|
|
|
A simple trick is to use COALESCE which returns you the first none null entry in a sequence of values, so you would replace the reference to @abc with COALESCE(@abc, '').
|
|
|
|
|
try,
set @txt = replace(@txt,'@fld',isNull(@abc, 0))
take 0 or any other value if you find null value
hope it helps
|
|
|
|
|
Any operation on null will yield null. Set the initial value of @abc to something other than null.
|
|
|
|
|
Hi.
I have a stored procedure that queries a table that takes about 35 seconds to return the results, meanwhile, from other stored procedures, i cannot insert new rows into that table, how is this possible to do? (even if that mean that on the large results the new records will not appear)?
Thanks.
|
|
|
|
|
Depends on what you are trying to achieve. From what I hear your select statement is locking most of the rows in the table, which could cause a full table lock (read level). That would mean your insert would fail as it cannot get an exclusive lock on the table. The other way around is also possible as you have experienced. If the insert starts first the select will fail.
You could potentially fix this by hinting to the SQL Server to use no locking on the select (add with nolock after the from part from the select), but this will cause you to get dirty data and uncommited data.
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
