Temporary Indexes?
Apr 11, 2008
are there such things as "temporary indexes"?
DBA noo13 alert: i've never heard of them, but it won't surprise me to learn that they exist
on the other hand, the guy who posted this could just be dreaming...in addtiton to Oracle, Sybase and MS SQLServer employ temporary indexing when a needed index has not been implemented.
that would be a lovely trick, wouldn't it? a join query is particularly slow because todd forgot to declare indexes, and the dbms picks up on this and creates them on the fly...
View 14 Replies
ADVERTISEMENT
Jul 1, 2014
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
View 5 Replies
View Related
Mar 14, 2004
I have a table that includes the html-output of different parts of my pages. This table grows very big very fast, and rows older than 24 hours are useless.
My question is if it is possible to have temporary rows, whose are automatically deleted after these 24 hours? And then how to accomplish that?
View 2 Replies
View Related
Sep 18, 2007
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
View 4 Replies
View Related
Jun 25, 2015
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
View 5 Replies
View Related
Sep 17, 2006
What is the difference please?
View 1 Replies
View Related
Feb 9, 2004
I have three aspx pages with one form in every page, these three pages allows to register a user. Now when I submit every form on every page the data is sent to the database, but somebody has recommended me to store data in a temporary database file, and when user submit last page (form) store all the data in main database.
I use Sql 2000 Server Personal Edition and VB.NET for aspx pages.
How can I use this temporary database in my aspx page?
Now my first form-page:
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
Dim strConnection As New SqlConnection(ConfigurationSettings.AppSettings("myConn"))
Sub Send_data(sender As Object, e As EventArgs)
Dim CmdInsert As New SqlCommand("new_user1", strConnection)
CmdInsert.CommandType = CommandType.StoredProcedure
Dim InsertForm As New SqlDataAdapter()
InsertForm.InsertCommand = CmdInsert
CmdInsert.Parameters.Add("@Id", SqlDbType.bigint)
CmdInsert.Parameters("@Id").Direction = ParameterDirection.Output
CmdInsert.Parameters.Add(New SqlParameter("@e_mail", SqlDbType.varchar, 50, "e_mail"))
CmdInsert.Parameters("@e_mail").Value = mail.Text()
……….
strConnection.open()
CmdInsert.ExecuteNonQuery
Dim IdUser As integer = CmdInsert.Parameters("@Id").Value
strConnection.close()
End Sub
Thanks
View 10 Replies
View Related
Mar 15, 2004
Can you create a temporary table using an ad-hoc query?
What I am trying to do is a type of filter search (the user has this and this or this) were i will not know how may items the user is going to select until they submit....that is why i can't use a stored procedure(i think)....any help on how to do this?
Thanks,
Trey
View 8 Replies
View Related
Sep 12, 2004
Hi could anyone give me a hint what does term "temporary table" mean regarding sql server?
View 1 Replies
View Related
Sep 21, 2004
I am having a problem with a script I have written. The script adds several temporary fields, performs some updates and then drops the temp fields. The problem is that Query Analyzer attempts to validate the update statements before the fields have been added and errors out with Invalid Column Name. The script works fine if I execute the alter statements first and then the update statements. Is there a trick to tell Enterprise Manager not to validate the statement until it is ready to execute it.
I believe I recall having a similar problem in Stored Procedures a while ago.
Thanks,
Ken Nicholson
View 1 Replies
View Related
Nov 7, 2005
Hi all,
how can i execute this query without errors??
create table #luca(c int)
drop table #luca
select * into #luca
from anagrafica_clienti
The error lanched is:
Server: Msg 2714, Level 16, State 1, Line 6
There is already an object named '#luca' in the database.
Why if i drop the table?How can i do?Thanks guy
View 3 Replies
View Related
Jan 15, 2007
Afternoon.
I'm having trouble with a query and ASP. The query itself is easy. I need a temporary table to be filled with the contents of a select and then i need to select out of the temporary table and return the results to the ASP.
So:
Code:
DECLARE @RESULTS TABLE (
ItemID int,
ItemDescription char(50),
ItemType int,
ItemRequestedBy char(50),
ItemStatus int,
ItemQuantity int,
ItemCostPer money,
ItemOrderNumber int,
DateAdded smalldatetime,
DateLastEdited smallDateTime
)
INSERT into @results (ItemID, ItemDescription, ItemType, ItemRequestedBy, ItemStatus, ItemQuantity, ItemCostPer, ItemOrderNumber, DateAdded, DateLastEdited)
SELECT * from cr_EquipmentData
SELECT * from @results
When I run this in Query Analyser, I get exactly the results I need... But when I try and run the ASP script, I get an error about the recordset not being open. (It's not the ASP checking 'cos when I run a simple select statement it works)
I appreciate there is no use for the query as it stands, but eventually I want to be able to perform not destructive statements on the @results table before returning the data to ASP. This is more 'testing' than anything at the mo'
Has anyone got any ideas?
Thanks...
View 3 Replies
View Related
Feb 1, 2004
Hi
I am using SQL 2000 I have the following code. When saving it, I am getting an error:
There is already an object named '##tbl' in the database
This is although #tbl is dropped.
Is threrea way to avoid this error? the only work around I found was to create a string with the SQL command and call EXEC, but I don't like this solution as it prevents early compilation of the procedure.
declare @x int
set @x=1
IF @x=0
begin
create table #tbl (
abc int
)
drop table #tbl
end
IF @x=1
begin
create table #tbl (
abc int
)
drop table #tbl
end
Than you.
View 5 Replies
View Related
Feb 10, 2004
Hi !
I use Sql 2000 Server as a database and an ASP.NET application with VB.NET language.
Now I am working with three pages with one form in every page that allows to register a user. To accomplish the registration the user needs to fill all the three pages, but now I am sending the data to the database in every page, so if a user leaves the process before reaching the third page it will have an invalid user entry into the database that I don’ t want. To avoid this I was recommended to store the in a temporary database file. I have been searching information about this but I have not found it.
Somebody can help me finding the necessary documentation to achieve it please?
Thanks
View 4 Replies
View Related
Apr 14, 2004
I am writing a stored procedure that outputs it's information to a temporary table while it assembles the information. Afterwards, it returns the contents of the temporary table as the results of the stored procedure.
I found that if you create the table, inside the SP, as an ordinary table, the information builds to that table considerably faster than if you use a true temporary table.
I found that if I create a user function that returns a table as it's return value, it is also as slow as if I used a true temporary table.
The database can amass over 2 million records in one table in just a few days. If I have the procedure query against this table, and output to an ordinary table it creates, and summarize the information it is adding to the table, then it takes an average of around 4 minutes to return the results from the query. If I change the output table to a temporary table (#temp), it between 12 and 15 minutes. Nothing else in the procedure changed. Just the kind of table. If I take the logic and move it to a function which returns those results in a RETURN table, it also takes over 14 minutes.
Why would it take so much longer outputing to a temporary table rather than a normal table? Is it because temporary tables are stored in a different database (tempdb)? Why would returning query results from a function be just as slow?
View 14 Replies
View Related
May 25, 2004
How can I view logs of local (to a session) temporary that are created/dropped?
View 6 Replies
View Related
Nov 8, 2005
Hi
I have to create a temporary table for generating a report in VB.
Pls help how to check the temporary table name in database.
I want if exits than drop and create a new one.
thanks
asm
View 3 Replies
View Related
Apr 11, 2006
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
===================================================
1000| Sales | -20,000.00 | 01 | 2005
===================================================
1000| Sales | -10,000.00 | 02 | 2005
===================================================
1001| Cost | 5,000.00 | 01 | 2005
===================================================
1001| Cost | 5,000.00 | 02 | 2005
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Description | Period 01 | Period 02 | Year to Date
=========================================================
Sales | 20,000.00 | 10,000.00 | 30,000.00
Total Sales | 20,000.00 | 10,000.00 | 30,000.00
Cost | 5,000.00 | 5,000.00 | 10,000.00
Total Cost | 5,000.00 | 5,000.00 | 10,000.00
=========================================================
Profit | 15,000.00 | 5,000.00 | 20,000.00
The above report would list 4 columns, with the last column being a calculated field as a sum of
Period01 + Period02 Amount, sorted by GL_ID and group under a summation row called
Total Sales & Total Cost.There would be a net amount appearing as Profit (Total Sales-Total Cost).
Guys, hope someone out there can help me with the sql command for the above report?
View 2 Replies
View Related
Jun 20, 2007
How would someone set up a temporary table from a database in order to then run a query against it to get more accurate data??
View 11 Replies
View Related
Jul 29, 2007
Hi,
i have:
[first table]
user_id
user_name
user_password
[second table]
set
item
order
Since the second table is somehow only temporary table (having at the front ajax script), i need the third table to store user's ID and all the info from second table.
What would be the easiest way to do it, since the first table contains only one row per user and the second one 40 rows per user.
thank you for your thought!
View 2 Replies
View Related
Aug 21, 2007
Hi,
I am trying to join two tables usig two temporary tables.I want the Output as table2/table1 = Output
Select temp2.Value/temp1.Value as val
from
(
(
select count(*)as Value from [Master] m
inner join [Spares]s on s.SId=m.SID
where m.Valid_From between '2007-06-01' and '2007-06-30'
)temp1
Union
(
select isnull(sum(convert(numeric(10,0),s.Unit_Price)),'0') as Value
from [Order] h
inner join [Spares] s on s.Number = s.Number
where h.Valid_Date between '2007-06-01' and '2007-06-30'
))temp2
as t
I could not find the output..
Plz help me..
Thanks..
View 3 Replies
View Related
Nov 2, 2007
Hello,
Our java application is running on oracle and now we would like to port it to sql server 2000. In oracle we have a global temporary tables that has an option on commit to delete rows.
Now I am looking for the same option in sql server but as far as I can see sql server's local temporary tables are visible per connection. Since the connection are shared in the pool it seems I can not rely on local temporary tables since the connection does not get closed at the end of the user's session. I need something that is visible per transaction not per connection.
Is it a better approach just to create a regular table and basically have a trigger to delete all data on commit?
View 2 Replies
View Related
Nov 27, 2007
Hi,
my code looks like this
Declare @Temp Table(id_customer int, id_product varchar(10))
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC104')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC143')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC103')
Insert Into @Temp(id_customer, id_product) Values(13, 'ABC102')
Insert Into @Temp(id_customer, id_product) Values(14, 'ABC101')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC10')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC11')
select * from @Temp
Declare @results VarChar(100)
Select @results = IsNull(@results + '; ', '') + IsNull(id_product, '')
From @Temp
where id_customer = 12
Select @results as all_products
and when i run "Select @results as all_products" what shall i do to get id_customer along? if i do "select id_customer, @results as all_products" I get error in return :(
thank you
View 5 Replies
View Related
Jul 20, 2005
If a stored procedure invokes another stored procedure that creates atemporary table why can't the calling procedure see the temporary table?CREATE PROCEDURE dbo.GetTempASCREATE TABLE #Test([id] int not null identity,[name] as char(4))INSERT INTO #Test ([name]) VALUES ('Test')CREATE PROCEDURE dbo.TestASEXEC dbo.GetTempSELECT * FROM #Test -- Invalid object name '#Test'.Thanks,TP
View 4 Replies
View Related
Jul 20, 2005
Hi, just a quick question regarding performance and speed.Q. Run a complicated query three times or create a TEMPORARY table andaccess it as needed?I have a page where it will be accessed 10,000+ a day.In that page I have an SQL query where it involves 3 different tables(approximate table sizes T1) 200,000 T2) 900,000 T3) 20 records)I'll be running that query 3 times in that page...One to retrieve the content and display it on the page.Second to count the number of records (using COUNT(*) function)And third to retrieve the content regions. (using DISTINCT function)What would be the best way of doing...Running the SQL query 3 timesOrCreate a temporary table and access it as many time as I needRegards,
View 4 Replies
View Related
May 8, 2007
Hello,
I would like to know from other members whether they are successful in using temporary tables within a stored procedure and use that stored procedure in a report.
My scenario is like this:
I have a stored procedure A which fetches the data from different tables based on the orderno passed as input parameter.
I have built another stored procedure B with a temporary table created and inserting the rows in to this temporary table based on vendor related specifc orders by calling the above procedure A.
I have used this stored procedure in the dataset created and getting this error:
Could not generate a list of fields for the query. Check the query syntax, or click the Refresh Fields on the query toolbar.
Does anybody encountered this and have a resolution.
Thanks in advance.
View 11 Replies
View Related
Dec 12, 2007
Hi All
declare @temp table
([EVENT_TYPE_ID] [int],
[Desc] [nchar](50) NOT NULL,
[Lead_Time] [smallint] NULL)
INSERT @temp
SELECT *
FROM TBL_EVENT_DEFINiTION
The table definition for @temp is the same as TBL_EVENT_DEFINiTION. The problem is I need to add a field to the @temp table and define the values using an update statement. If I include the additional field in the @temp table declaration then I get an error saying something to the effect of the number of fields is different. Is there a way of altering the temporary table to add this field?
Many thanks in advance
Alex
View 4 Replies
View Related
Aug 20, 2007
Hello,
I need to create functions in SQL Server 2000, but I do not have permission.
Is there any way to work around? It seems there is no temporary function.
Can temporary sp, or temporary tables help?
Thank very much!
-ZZ
View 3 Replies
View Related
Feb 12, 2007
hi!
i used some temporary table in store procedure (sqlserver 2005)
our team have report software calisto .
the calisto use crystal and reports which use
this store procedure .
because of that,
we have list of many temporary table with the same name
#dbo.sug_name ... ,#dbo.sug_name ... ,......
in the system database .
what could be the reason for that and how can we drop it ?
Msg 3701, Level 11, State 5, Line 2
Cannot drop the table '#sug_name', because it does not exist or you do not have permission."
View 10 Replies
View Related
May 15, 2006
Hello!
I'm creating a temporary table in a procedure to help me with some calculations. I would like the name of the temporary table to be 'dynamic', I mean, to have the hour it was created, something like this: create table #myTempTable15May11:10:00, so if someone access the procedure while it's running, he won't have problems in creating his 'own' temporary table (create table #myTempTable15May11:10:02). Is there anyway I can do this?
Thank you!
View 5 Replies
View Related
Apr 26, 2007
Hello,
I tried to make a DTS to transform data in a text file, I used a Store Procedure that use a temp table (#Resultados) but the DTS give me an error.
I read that in this case I can´t use local temp tables but I can use global temp tables, then I changed in my Store, #Resultados by ##Resultados, bu the result was the same.
My Store is likely to his. Please help me.
INSERT ##Resultados (Planta, Etapa,GrupoEquipo,Equipo,Concepto,Fecha,Guardia,
Valor,idConcepto)
EXEC CalculosDiarios @Area,@Reporte,@FechaIni,@FechaFin,0
SELECT LEFT(RP.Grupo,3) + LEFT(RP.Equipo,12) + LEFT(RP.SubGrupo,2)
+ LEFT(D.Fecha,8) + D.Valor as Dato
FROM
ReportesPlantilla RP
LEFT JOIN
##Resultados D
ON
RP.Planta = D.Planta
AND RP.Etapa = D.Etapa
AND RP.GrupoEquipo = D.GrupoEquipo
AND RP.Equipo = D.Equipo
AND RP.Concepto = D.Concepto
AND D.Fecha BETWEEN @FechaIni AND @FechaFin
View 6 Replies
View Related
Jul 23, 2007
Can some one tell me how to display the fields of temporary table in the report? In the query a global temp table is created and in the end i am displaying the fields of that table. How do i do that?
View 1 Replies
View Related
