Hello -- thank you for taking the time to read this.
I have a very large table that is used both for archives and new information. To get the current information, the table is queried by many different users at various polling periods. The SELECT required includes about fifteen JOINS, and only returns about 200 rows at any given time.
So I got to thinking if it might be faster to periodically run the big query as a SELECT INTO into a smaller table and letting the polling clients query the smaller table with SELECT *. Periodically, the smaller table would be DROPPED and refereshed with another SELECT INTO.
Trouble is, the data would have to be updated once every 30 seconds, and there are inbound polls coming at the rate of about 200 per minute. It got me to thinking what might happen if a client attemtped to query the smaller table when it was in the process of being dropped and refilled.
So my question is three-part:
1) assuming a larger table of about 500,000 records and only 500 pertinent at any given time, is there any real potential of performance enhancement by switching to a SELECT INTO table?
2) if so, is there a chance of a client failing a query if the inbound query somehow collides with the DROP/SELECT INTO procedure?
3) if so, is there any way to prevent it or a better way of doing this?
Thanks again for reading, and in advance for any help you can provide. I apologize if I sound like a dummy - it's hard to fake intelligence!
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
I'm confused as to how this sp is working? Can you drop a table and then select into it without recreating it? I thought when you dropped a table it was gone.
sp: drop table tmp_claimeligiblebalance
SELECT tsd_Claim.clpid, Sum(tsd_Claim.cloutstandingamt) as sumoutstanding INTO tmp_ClaimEligibleBalance
FROM tsd_Claim
GROUP BY tsd_Claim.clpid HAVING sum(tsd_Claim.cloutstandingamt) <>0 AND sum(tsd_Claim.cloutstandingamt) IS NOT NULL And max(tsd_Claim.clfromdos) < (cast(getdate() as smalldatetime) - 120) And max(tsd_Claim.clins) Is Not Null
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
Hello all, I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.
select distinct ProductType from Product order by ProductType
We are in the process of upgrading a sql 2000 database over to 2005 and have noticed some substancial performance drops with scalar udfs in 2005.
I have already read the following post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=491984&SiteID=1 and recognise that udfs are not the most performant option in the first place, but was surprised how much slower these have become on 2005.
Has anyone else had this sort of issue, we really don't want to go away from the udf's but would like to know if there is a design issue within a udf that might be causing this (or even a usage issue). What I am getting as is: Is there certain types of queries, or keywords that should be avoided in udfs on 2005?
A simple example we have is a udf that returns an exchange rate stored in the db, this has parameters of "from currency", "to currency", and date.
SET @ret = ( SELECT TOP 1 TELE_TRANSFER_RATE
FROM dbo.EXCHANGE_RATES
WHERE EXCH_CURRENCY_NO = @from
AND CURRENCY_NO = @to
AND RATE_DATE <= @date
ORDER BY RATE_DATE DESC )
RETURN @ret
And then this is called from a script that returns financials, standard select statement, with udf call in select clause.
There are several parameters on a report. One of the parameter is a multi-select enabled parameter and I suppressed the value "All" showing as one of the item in the drop down list, simply by filter out the [bha].[bha].CURRENTMEMBER.LEVEL.ORDINAL to 1, as "(Select All)" is pre-assigned to the drop list when multi-select is enabled and it is confusing to show "(Select All)" and "All" in the drop list. However I have another report which is linked to this report and the value which is required to pass to this report for this parameter is "All". Can I pass the "Select All" as a parameter from the other report? If so, how? Thanks.
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database. The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records. I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered). I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these). I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes. I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem. So my question is: Is it normal for such a type of read query to take 90 seconds to complete? Is there anything I could do to speed it up. Any other thoughts? Thanks
Hi, Has the inability to change the width of the multi-select drop down for parameters been fixed yet? Even if I could at least make it default to be wider would be great. Thanks.
Hi Will somebody please explain how to combine asp.net dropdown lists to write a SQL database select query. I am using VWdeveloper and C Sharp. For example, say I have 3 dropdownlists on my webpage as below, List 1, Cities, London, Rome, Barcelona etc List 2, Restaurants by Type, Italian, chinese, Indian etc List 3, Number of tables/ seats 10-20, 20- 40, 50 -100 I want someone to be able to search for a restaurant by selecting an item from each dropdownlist such as, "Barcelona" "Italian" "50-100" This search query would return all the Italian restaurants in Barcelona with 50-100 tables/seats. I would also like the select query to work even if one of the dropdownlists items is not selected. Hope somebody can clear this up? Also would sql injection attacks be a threat by doing it this way? Thanks all
I am really new to SSRS (This is my first report) and I am looking to create a drop down that will allow a user to select the value to appear in a report. Meaning that is my values are:
a b c d
If the user types a B it would "jump to that letter in the selection.
which one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance, Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
I have a couple questions. I'm very new to SQL and I have this problem: I need to be able to drop the contents of Existingtable_B into Newtable_A - I found this command (below) that will make a 'copy' but I don't want to keep the contents of Existingtable_B. Is it possible to drop them into Newtable_A instead of copy? Also, I want to do this for 5 tables on Sundays at midnight.. how could I schedule that? Finally, what happens if there is not enough space or some other critical error happens during this procedure? I don't want to lose the data.
Hi,When I drop a table in Sqlserver 2000 database, The following error occurs:Server: Msg 1204, Level 19, State 1, Line 1The SQL Server cannot obtain a LOCK resource at this time. Rerun yourstatement when there are fewer active users or ask the system administratorto check the SQL Server lock and memory configuration.What's wrong? Any help is greatly appreciated, thanks.
Hi everybody,I need some help in SQL Server. I am looking for a command that will "Dropall user table" in auser database.Can anyone help me?Thank you very muchSabrina
I am unable to get the table in the following VB code to actually DROP. A straight SQL version with literals (no variables) runs "successfully" as a query but the table also fails to DROP. Can anybody explain what I'm doing wrong?
i am using vb.net and ms sql server 2005 express.....what is the syntax for dropping a table if existsi have used this but it says incorrect syntax near if Dim cmda As New SqlCommand("drop table " + test + " if exists", New SqlConnection(strdb)) cmda.Connection.Open() cmda.ExecuteNonQuery() cmda.Connection.Close()any solutions???? plz only answer in vb.net and sql server express
I'm trying to drop a temporary table. I keep getting this error: Cannot drop the table '#temp_table', because it does not exist in the system catalog. I tried the following but it did not work, so help would be appreciated. if object_id('tempdb..#temp_table') is not nulldrop table #temp_table
I need some help with Merge Replication. After successfully defining a publisher, distributor and subscriber to perform merge replication, we decided to test Merge Replication to see what we can and can't do.
What we found is that you can't add new fields or change the nullable/null attributes of tables whilst the replication settings are still defined to the databases. ie you get the following error
'tblProducts' table - Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tblProducts' because it is published for replication.
So we thought, fair enough. We decided to uninstall replication using the wizards provided to see whether we can make schema changes. What we found is that we can make schema changes to the publisher database(on Server1), BUT not to the subscriber (Server2).
Does anyone know why? We are running SQL7 SP2. Email me on ftowicz@icontact.com.au
We have a table created by an application, and a view that joins the table with other tables.
For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.
However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.
We have a publication from database a to database b. Database a containts table1 to be published to databasae b. Database b contains a publication of table1 to database c. When we go to rerun the publication from database a it errors saying cannot drop table1 since it is part of database b's publication. how do you do this.
I had a question and did not obtain an answer. So I am trying to rephrase and ask again, in case I was not clear the first time.
When we drop and recreate a table, do we always have to recompile the stored procedures that reference the table? Or is only under certain scenarios that we need to do so- like if an index on the table is changed Please let me know Thanks in advance Kiran
