hi, what are the tools that I can use to Optimize the query/ index.
I know that if I am running a query on a table I create index on the fields where I use in the where clause, is this a right thinking.
Someone told me how do I determine what columns should be indexed, I told him the fields that I use in the where clause should be indexed to speed up the process of retrieving the data. ..... is this answer correct. if not please advice the correct one.
Thanks
Hello, I am hoping someone here can help me optimize the following query: SELECT INCOMING.DATE_TIME, INCOMING.URL, INCOMING.HITS, USER_NAMES.USER_LOGIN_NAME, CATEGORY.NAME FROM (wsHQMay2004.dbo.INCOMING INCOMING INNER JOIN wsHQMay2004.dbo.CATEGORY CATEGORY ON INCOMING.CATEGORY = CATEGORY.CATEGORY) INNER JOIN wsHQMay2004.dbo.USER_NAMES USER_NAMES ON INCOMING.USER_ID = USER_NAMES.USER_ID WHERE INCOMING.DATE_TIME >= '2004-05-01 00:00:00.00' AND INCOMING.DATE_TIME < '2004-06-01 00:00:00.00' ORDER BY INCOMING.URL ASC
I am just hoping to get some tips on perhaps a better way to write this query as right now, due to the size of the incoming table, this query just takes forever.
we have an insurance program up and running in our regions and we get random reports of slowness. in an effort to track down all facets of slowness i am looking at all my sql code to make sure it is as efficient as possible. I know a little about SQL and writing SQL statements, enough to help me do my job well. but i do not write optimized code.
if request.form("selPolicyNum") <> "" then sqlPolicyInfo = "SELECT PIEffectiveDate, PIExpirationdate from PIMaster where PIPolicyNum='" & request.form("selPolicyNum") & "'" Set rsPolicyInfo = Server.CreateObject("ADODB.Recordset") Set rsPolicyInfo.ActiveConnection = webLookupConn 'rsPolicyInfo.CursorType = adOpenDynamic 'rsPolicyInfo.LockType = adLockOptimistic rsPolicyInfo.Source = sqlPolicyInfo 'rsPolicyInfo.CursorLocation = adUseClient rsPolicyInfo.Open 'response.write sqlPolicyInfo end if
that is the code used to store a remark into the system. is this code optimized already or should some of the db parameters be changed to make things faster? this is just an example of many of the SQL statements that i may or may not have to fix. thank you for any and all help.
I have the following query that works fine but i'm wondering if there is a way to optimize it better as when I analyze through sql profiler it is at the top of the list of using the cpu
SELECT DISTINCT site, d, (SELECT COUNT(id) FROM anP aPV2 WHERE aPV2.confirmed=1 and aPV2.stage=2 and aPV2.inserted=0 and aPV2.site=aPV1.site and aPV2.d>=aPV1.d and aPV2.d<=aPV1.d) AS mycount FROM anP aPV1 WHERE confirmed=1 AND stage=2 AND inserted=0 ORDER BY site,d
I have the below query written so that i do not insert entries that is already existing in the table. I am trying to put in 70000 entries at a single shot and it breaks down. Can anybody help me optimize the below query so that it doesnt break? Is there any other way I can write this query?
Please do help me with this. Thanks in advance. The table in which i am inserting the entries has a composite key composed of ACCT_NUM_MIN and ACCT_NUM_MAX. I am getting this from a table which doesnt have a primary key(CORE)
INSERT INTO CRF (CORE_UID,ACCT_NUM_MIN, ACCT_NUM_MAX,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE) SELECT UID , LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), BIN, BUS_ID, BUS_NM, ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE FROM CORE o WHERE NOT EXISTS (SELECT * FROM CRF i WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX)
To start with, I'll give a simplified overview of my data.BaseRecord (4mil rows, 25k in each Region)ID | Name | Region | etcOtherData (7.5mil rows, 1 or 2 per ID)ID | Type(1/2) | DataProblemTable (4mil rows)ID | ConcatenatedHistoryThe concatenated history field is a nvarchar with up to 20 differentpipe delimited date/code combinations, eg. '01/01/2007X|11/28/2006Q|11/12/2004Q|'Using left outer joins (all from base, the rest optional) I've got aview something like:View (4mil rows)ID | Name | Region | etc | Data | Data2 | ConcatenatedHistoryQuerying it, it takes about 15-20 seconds to do this:Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory
selected_item_id as int (PK), cust_id as int (FK), item_id as int (FK), ...
-------
With the following query:
select cust_ID from selected_items_tbl WHERE item_id in (1, 2, n) GROUP BY cust_id, item_id HAVING cust_id in (select cust_id from selected_items_tbl where item_id = 1) AND cust_id in (select cust_id from selected_items_tbl where item_id = 2) AND cust_id in (select cust_id from selected_items_tbl where item_id = n)
-------
Each of these tables has other items included. Selected_Items_tbl holds zero to many of the items from the item_tbl for each customer. If I am searching for a customer who has item 1 AND item 2 AND item n, what would be the most efficient query for this? Currently, the above query is what I am working with. However, it seems that we should be able to do this type of search in a single query (without subqueries).
I'm trying to get a query to run which looks at completed orders that have not had another paid order in 180 days. The database I'm running it against is very large so I can't get it to complete. Where's what I've got:
select Date =cast(cl1.cl_rundate as datetime(102)),count(cl1.cl_recno) as 'Completed Initials', cl1.cl_status as Status from dbo.vw_Completedorders cl1 where cl1.lob_lineofbusiness = 'aaa' and cl1.cl_rundate > '20060801' and not exists ( select cl2.cl_company from dbo.vw_Paidorders cl2 where cl2.lob_lineofbusiness = 'aa'and cl2.cl_company = cl1.cl_order and cl2.cl_rundate > '20060101' and datediff(day,cl2.cl_rundate,cl1.cl_rundate) < 180) group by cl1.cl_status, cl1.cl_rundate
Hi I have one table (tableDemo) with following structure: TSID bigint (Primary Key)TskID bigint Sequence bigint Version bigint Frequency varchar(500) WOID bigint DateSchedule datetime TimeStandard real MeterEstimated real MeterLast real Description ntext CreatedBy bigint CreatedDate datetime ModifiedBy bigint ModifiedDate datetime Id uniqueidentifier I have 8000 records in this table. When I fire simple select command (i.e. select * from tableDemo) it takes more than 120 seconds. Is there any techniques where I can access all these records within 2-3 seconds ? Regards, ND
We have SQL Server 2000 and int is an Oracle linked server. I'm trying to run the following query...
SELECT DISTINCT a.auf_nr AS OrderNo, e.ku_name AS Customer, d.bestell_dat AS OrdDate, d.liefer_dat AS DelvDate, CAST(SUM(b.anz) AS FLOAT) Qty, CAST(SUM((CAST(c.breite AS FLOAT) / 1000 * CAST(c.hoehe AS FLOAT) / 1000) * b.anz) AS FLOAT) SQM, CAST(SUM(a.liefer_offen) - (SUM(a.anz) - SUM(b.anz)) AS FLOAT) AvailDelv, CAST(SUM(a.liefer_anz) AS FLOAT) Delvd, CAST(SUM(c.sum_brutto*a.anz) AS FLOAT) Value
FROM liorder..LIORDER.AUF_STAT a, liorder..LIORDER.AUF_LIP_STATUS b, liorder..LIORDER.AUF_POS c, liorder..LIORDER.AUF_KOPF d, liorder..LIORDER.KUST_ADR e
WHERE a.auf_nr = b.auf_nr and b.auf_nr = c.auf_nr and c.auf_nr = d.auf_nr and d.kunr = e.ku_nr and a.auf_pos = b.auf_pos and b.auf_pos = c.auf_pos and b.lip_status = 7 and c.ver_art !='V' and a.history = 0 and a.rg_stat != 2 and e.ku_name IS not null and e.ku_vk_ek = 0 and d.bestell_dat BETWEEN '01/01/2005' and '12/17/2005'
GROUP BY a.auf_nr, d.liefer_dat, b.lip_status, d.bestell_dat, e.ku_name, d.kopf_tour, d.kopf_firma
HAVING CAST(SUM(a.liefer_offen)-(SUM(a.anz)-SUM(b.anz)) AS FLOAT) > 0
..and it takes around 2 minutes to show the results even if the date range is of the same date. I even tried to use an indexed column but I still get the same slow execution time. I even tried to create a UDF so that the WHERE clause would be resolved remotely on the Oracle DB but still the same. Is there anyway I can do it in much more efficient and faster way?
I've got a SQL 2005 table(DataTable) with 5.5 million rows and a clustered index on an integer column. The column is a foreign key pointing to another table (LookupTable) that associates the integer with an nvarchar(50) column. Both integer and nvarchar(50) columns are in a clustered index.
I'm running equivalent select statements with slightly different where clauses, and am not seeing the performance similarities I'd expect.
select * from DataTable join LookupTable on DataTable.Integer = LookupTable.Integer
1. Setting the where clause to the nvarchar(50) value on the LookupTable takes 64 seconds. 2. Setting the where clause to Integer = (select integer from LookupTable where nvarchar(50) = 'mysearchstring') takes 61 seconds. 3. Setting the where clause to Integer = 526 takes 1 second.
Is this poor design in SQL, or is it a misconfiguration? I would have expected SQL to modify my query to essentially be #3 in all cases before touching the DataTable, which would cause all three versions to take 1 second.
The duplicates that this thread relates to are the kind with duplicate "keyword" entries AND dissimilar field entries; i.e. :
Code:
keyword negative exact broad Phrase Polo 0 122 4 Polo 0 122 5
I've come up with an SQL query that seems to return all of these duplicates (save one of each type- the 'real', unique entry). However I think I made the query very inefficient. My SQL is very bad; this query will be running over tens of thousands of rows, so if it can be at all optimized I would greatly appreciate your help!
What I have so far is:
Code:
string query1 = "SELECT * FROM TableName" + " WHERE EXISTS (SELECT NULL FROM TableName" + " b" + " WHERE b.[keyword]= " + "TableName"+ ".[keyword]" + " AND b.[negative]<> " + "TableName"+ ".[negative]" + " ORb.[keyword]= " + "TableName"+ ".[keyword]" + " ANDb.[exact]<> " + "TableName"+ ".[exact]" + " ORb.[keyword] = " + "TableName"+ ".[keyword]" + " ANDb.[broad]<> " + "TableName"+ ".[broad]" + " ORb.[keyword]= " +"TableName"+ ".[keyword]" + " ANDb.[phrase]<> "+"TableName"+ ".[phrase]" + " GROUP BY b.[keyword], b.[broad], b.[exact]" + " HAVING Count(b.[keyword]) BETWEEN 2 AND 50000)" ;
the algoritm seems to check every column of every row in order to determine a duplicate. Seems straightforward to me, but alas slow...
Is there a better/faster way I can do this? Thanks for you help!
Hello everyone! I've got a problem with a real slow query, I would be very happy if somebody has any idea to improve the speed of it... The idea is to get the top 2 products, a customer hasn't bought wich are in his interest...
query (simplificated) ------------------------------------------------- SELECT TOP 2 prodID, Title, Price FROM bestSold7Days WHERE prodID NOT IN (SELECT prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID=394)) AND (prodType = COALESCE((SELECT TOP 1 products.prodID FROM orders INNER JOIN orderProducts ON order.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID=394) GROUP BY products.prodType ORDER BY SUM(orderProducts.PCS) DESC), 2)) ------------------------------------------------- end query
(COALESCE is for replacing if the customer hasnt ordered anything, or hasnt ordered anything of this type)...
I have a DELETE statement that deletes duplicate data from a table. Ittakes a long time to execute, so I thought I'd seek advice here. Thestructure of the table is little funny. The following is NOT the table,but the representation of the data in the table:+-----------+| a | b |+-----+-----+| 123 | 234 || 345 | 456 || 123 | 123 |+-----+-----+As you can see, the data is tabular. This is how it is stored in the table:+-----+-----------+------------+| Row | FieldName | FieldValue |+-----+-----------+------------+| 1 | a | 123 || 1 | b | 234 || 2 | a | 345 || 2 | b | 456 || 3 | a | 123 || 3 | b | 234 |+-----+-----------+------------+What I need is to delete all records having the same "Row" when there existsthe same set of records with a different (smaller, to be precise) "Row".Using the example above, what I need to get is:+-----+-----------+------------+| Row | FieldName | FieldValue |+-----+-----------+------------+| 1 | a | 123 || 1 | b | 234 || 2 | a | 345 || 2 | b | 456 |+-----+-----------+------------+A slow way of doing this seem to be:DELETE FROM XWHERE Row IN(SELECT DISTINCT Row FROM X x1WHERE EXISTS(SELECT * FROM X x2WHERE x2.Row < x1.RowAND NOT EXISTS(SELECT * FROM X x3WHERE x3.Row = x2.RowAND x3.FieldName = x2.FieldNameAND x3.FieldValue <> x1.FieldValue)))Can this be done faster, better, and cheaper?
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WHERE a.AssociationGuid IN ( SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.
expanding the inner select into a list of guids the query runs instantly:
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WHERE a.AssociationGuid IN ( '0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4', '52C616C0-C4C5-45F4-B691-7FA83462CA34', 'C95A6669-D6D1-460A-BC2F-C0F6756A234D')
It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.
The tables involved:
Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so... Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets. AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.
I'd include .sqlplan files or screenshots, but I don't see a way to attach them.
I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE a.AssociationGuid IN ( SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
I'm not the most knowledgable DBA, I've had to learn almost completely on my own, AND on a production server, because it's the only MS SQL Server I have access to.
Everything was fine before I took down the production server for maintenance. Someone suggested that I re-index my tables because I was having some performance issues with a particularly large table (it didn't help that table btw), so I did re-index.
Now, Everything works wonderfully, except for the performance issue mentioned AND one other thing that is going horribly wrong.
Here is the table:
create table ABMcontactlink ( classifier varchar(20) not null, /* Classification of contact. */ transmitter varchar(36) not null, contact integer not null, /* Link to ABMcontact (detail) table */ primary key (classifier,transmitter,contact), foreign key (contact) references ABMcontacts(identifier), group_name varchar(20) null,
last_modification_date datetime, /* Date/time record last touched */ last_modification_id varchar(40) /* Who last touched record */ )
go create index IndexABMcontactlink on ABMcontactlink(classifier,transmitter) go
create index CandidateABMcontactlink on ABMcontactlink(transmitter) go
As you can see, I have the primary key, which creates a clustered index, PK_ABMContactlink_Some Number, and two other indexes.
Now, this is a very busy production database, and most quick short queries benefit more from CandidateABMContactlink than from the other two indexes.
Unfortunately, in this production system, and this table, seconds count ALOT, so when I have roughly 3000-4000 quereies an hour pulling information from this table, I personally beleive I need to keep CandidateABMContactlink, and I'm not willing to find out on a production server.
** Now to the Problem at Hand **
I have one query that kicks off about 7 times a day, used to take less than 1 minute before the re-index. NOW it takes 30 Minutes. And it drags the system to a crawl.
I did some looking into it, and this query is using CandidateABMContactlink, and it takes 30 minutes. If it uses PK_Abmcontactlink it finishes in under 45 seconds.
Most queries are simple, "Select Column_names from abmcontacts where identifier in (select contact from abmcontactlink where transmitter = 'XXXXXX')"
This one is:
select * from ABMcontacts where ( (last_modification_date >= '2006-04-28 04:40:03' and last_modification_date <= '2006-05-09 16:41:14') and EXISTS(select contact from ABMcontactlink where contact = identifier and EXISTS(select transmitter_id from ABMtransmitter where transmitter_id = transmitter and (dealer = 'XXXX'))))
or (EXISTS(select contact from ABMcontactlink where (last_modification_date >= '2006-04-28 04:40:03' and last_modification_date <= '2006-05-09 16:41:14') and contact = identifier and EXISTS(select transmitter_id from ABMtransmitter where transmitter_id = transmitter and (dealer = 'XXXX'))))
I can't change the query, so how do I make it use the Index I want it to use without removing the index that it is using? (I know there are much better ways to write the above query, I'm not the culprit, if I could re-write it, I would)
I have indexed my SQL Server tables to gain some speed on calling up tables and queries ( using VB and ADO ). It is still very slow...Is there a move I have to make once my tables are indexed or is there any tricks to improve the speed cause I am getting kinda desparate right now :(
I have a question in regards to optimistic locking:
I have a database conversion that will be running on a SQL 7.0 system. The process needs to be completed ASAP and to this end, I have tried to set up all aspects of the server to be geared towards speed rather than redundancy for the duration of the process (i.e. moving heavily used tables to separate filegroups on a RAID 0 set, dedicating a separate disk for the database log). I was now looking at trying to tweak locking behaviour to enhance performance (as for the duration of the conversion, no other user will be connecting to the database - the only initator of data changes will be the conversion application, which feeds statements serially to the server). As far as I know changing lock settings is something that would be initiated by the application itself, but is there any property I can set on the server to further enhance performance in this area?
We are evaluating a tool by Lechotech that can optimize sql statements. It is a pretty good tool, but we would like to compare it against some others. Has anyone seen any other such tools?
I'm no SQL wiz, just know basics to get me by ... What I'm trying to do is: everytime a record is inserted into an online orders table, that record needs to be inserted into another table in another database, but with added information.
This is the Trigger I came up with:
CREATE TRIGGER OtherDatabaseInsertTrigger ON dbo.t_order FOR INSERT AS
DECLARE @CLIENT VARCHAR(30) DECLARE @OrderNumberID INT SET @CLIENT = 'DevShed'
SET @OrderNumberID = (SELECT @@IDENTITY) UPDATE test2.dbo.t_order SET client = @CLIENT WHERE oid = @OrderNumberID;
I don't know if its possible to do an INSERT INTO SELECT with additional fields in the 2nd table, I was trying, but failed. Had to resort to the bottom piece of SQL to get the ID and run a separate query to add the additional items to the new record in table 2.
Any SQL masters out there that can help me make this better, or know of some other way to do this.
I've tried a bunch of different ways in an effort to stay away from using a cursor, but I haven't been able to accomplish what I need to do without one. So, I coded this process using cursors and performance (as expected) is pretty mediocre. I was wondering if someone could take a quick look and suggest a different approach or maybe suggest ways to optimize the current code.
SELECT T1.F3 FROM T1 INNER JOIN T2 ON T1.F4 = T2.F4 WHERE (T1.F1 > @iNum AND T2.F1 > @iNum) OR ( @iNum2 * (T1.F1 - T2.F1)/(T1.F2 - T2.F2) ) + (T1.F1 - ((T1.F1 - T2.F1)/(T1.F2 - T2.F2) * T1.F2) ) > @INum
As you can see, the second part of the WHERE (after the OR) is much more complicated than the part before the OR. My query would run a lot faster if it tried the first part of the OR and didn't bother with the second part if the first part was satisfied. Is there any way to do this?
SELECT * FROM OPENQUERY (liorder, ' SELECT DISTINCT a.AUF_NR AS OrdNo, e.KU_NAME AS Customer, a.AUF_POS AS Pos, f.PC_PANE_NO AS Pane, f.PC_SGGL_SEQ AS Component, f.PC_SGGL_COD AS GlassCode, d.GL_BEZ AS GlassDesc, a.ANZ AS Qty, ((c.BREITE/1000*c.HOEHE/1000)*a.ANZ) AS SQM, (a.ANZ*c.SUM_BRUTTO) AS Val, (CASE WHEN(SELECT SUM(h.KF_FERT_QTY) FROM LIPROD.KAPA_AUS_FERT h WHERE a.AUF_NR = h.KF_ORDER_NO AND a.AUF_POS = h.KF_ORDER_POS AND f.PC_PANE_NO = h.KF_SCHEIB_NR AND f.PC_SGGL_SEQ = CASE WHEN h.KF_SEQ_NR = 0 THEN 1 ELSE h.KF_SEQ_NR END AND h.KF_SCHR_NR IN (2, 402, 502, 602)) IS NULL THEN 0 ELSE(SELECT SUM(h.KF_FERT_QTY) FROM LIPROD.KAPA_AUS_FERT h WHERE a.AUF_NR = h.KF_ORDER_NO AND a.AUF_POS = h.KF_ORDER_POS AND f.PC_PANE_NO = h.KF_SCHEIB_NR AND f.PC_SGGL_SEQ = CASE WHEN h.KF_SEQ_NR = 0 THEN 1 ELSE h.KF_SEQ_NR END AND h.KF_SCHR_NR IN (2, 402, 502, 602)) END) AS Done FROM LIORDER.AUF_STAT a, LIORDER.AUF_KOPF b, LIORDER.AUF_POS c, LIORDER.GLAS_DATEN d, LIORDER.KUST_ADR e, LIPROD.AUF_POS_COMP f WHERE EXISTS (SELECT g.AUF_NR FROM LIORDER.AUF_STAT g WHERE g.AUF_NR = a.AUF_NR AND g.RG_OFFEN != 0) AND EXISTS (SELECT i.KF_ORDER_NO FROM LIPROD.KAPA_AUS_FERT i WHERE a.AUF_NR = i.KF_ORDER_NO AND i.KF_SCHR_NR IN (2, 402, 502, 602)) AND a.AUF_NR = b.AUF_NR AND b.AUF_NR = c.AUF_NR AND c.AUF_NR = f.PC_ORDER_NO AND a.AUF_POS = c.AUF_POS AND c.AUF_POS = f.PC_ORDER_POS AND b.KUNR = e.KU_NR AND f.PC_SGGL_COD = d.IDNR AND a.HISTORY = 0 AND b.AUF_OFF = 0 AND c.VER_ART != ''V'' AND e.KU_VK_EK = 0 AND e.KU_NAME IS NOT NULL ORDER BY a.AUF_NR DESC, a.AUF_POS ASC')
...It is retrieving data from an Oracle linked server. But the execution time is so friggin' long! I tried running it and for around 30 minutes it hasn't shown any results. So I could even tell the exact time it would take to return results. Do you have any tips regarding query optimization? Thanks in advance.
-- minatest table will contain indexes with fragmentation above 10% which need to be defragged -- this will go through all databases -- null indexes will not be affected
exec sp_msforeachdb' use ? INSERT INTO #minatest SELECT db_name(database_id), phystat.page_count, i.fill_factor, OBJECT_NAME(i.object_id), i.name, phystat.avg_fragmentation_in_percent, newfragmentvalue = 0, index_type_desc, index_level FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, ''DETAILED'') phystat JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 10 AND phystat.page_count < 10000 '
select @Counts = count(Databasename) from #minatest -- sets the maximum amount of fields to go threw as a number
declare targets cursor -- declare cursor with values to search through for select * from #minatest
open targets -- open cursor
fetch next from targets into @DatabaseName,@pagecount,@vfillfactor,@TableName,@IndexName,@FragmentPercentage,@vnewfrag,@index_type_desc,@index_level-- take rows from table
select @i=0 while @@fetch_status=0 and @i<=@Counts-- set loop condition
begin
select @sql = 'USE '+@DatabaseName+'; '+ ' ALTER INDEX '+@IndexName+' ON '+ @TableName+ ' REBUILD with (ONLINE=ON,SORT_IN_TEMPDB=ON,STATISTICS_NORECOMPUTE=OFF);' exec sp_executesql @sql select @nfsql = 'select @cnt = avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(NULL,NULL, NULL, NULL, ''DETAILED'') phystat JOIN '+@DatabaseName+'.sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id WHERE i.name='''+@IndexName+''' and index_type_desc='''+@index_type_desc+''' and index_level='''+CAST(@index_level as varchar(20))+'''' exec sp_executesql @nfsql ,@params, @cnt=@vnewfrag OUTPUT
update #minatest set newfragmentvalue = @vnewfrag where IndexName = @IndexName and TableName = @TableName select @i=@i+1
fetch next from targets into @DatabaseName,@pagecount,@vfillfactor,@TableName,@IndexName,@FragmentPercentage,@vnewfrag,@index_type_desc,@index_level-- take next field of table
end
close targets DEALLOCATE targets
ALTER TABLE #minatest DROP COLUMN index_type_desc,index_level select * from #minatest -- displays which indexes where defraged and their new frag value drop table #minatest
I have two tables.One has approx 90,000 rows with a field .. let's call in BigInt (and itis defined as a bigint data type).I have a reference table, with approx 10,000,000 rows. In thisreference table, I have starting_bigint and ending_bigint fields. Iwant to pull out all of the reference data from the reference table forall 90,000 rows in the transaction table where the BigInt from thetransaction table is between the starting_bigint and ending_bigint inthe reference table.I have the join working now, but it is not as optimized as I wouldlike. It appears no matter what I do, the query does a full table scanon the 10,000,000 rows in the reference table.Sample codeSELECT ref.*, tran.bigintfrom transactiontable tranINNER JOIN referencetable ref on tran.bigint betweenref.starting_bigint and ending_bigintYes, all 3 of the fields are indexed. I even have a composite index onthe reference table with the starting_bigint and ending_bigint fieldsselected as the composite.Any help would be appreciated.Robert H. KershbergIT DirectorTax Credit CompanyJoin Bytes! or Join Bytes! or Join Bytes!
Hello all,I have a table with thousands of rows and is in this format:id col1 col2 col3 col4--- ------ ----- ------ ------1 nm 78 xyz pir2 bn 45 abc dirI now want to get the data from this table in this format:field val---------------------------col1 nmcol1 bncol2 78col2 45col3 xyzcol3 abccol4 pircol4 dirIn order to do this I am doing a union:select * into #tempUpdate(select 'col1' as field, col1 as val from table1unionselect 'col2' as field, col2 as val from table1unionselect 'col3' as field, col3 as val from table1)the above example query is smaller - I have a much bigger table withabout 80 columns (Imagine the size of my union query :) and this takesa lot of time to execute. Can someone please suggest a better way to dothis?The results of this union query are selected into a temp table, which Ithen use to update another table. I am using SQL Server 2000.my main concern is performance. any ideas please?thanks
I have an application that's allows user input, and is translating it bystripping out the html tags and also doing some code translations. The useris able to later edit their input. However it's unfeasible to reversetranslate it back as the logic would be too complicated, and there areinstances where it won't be possible.So, what I'm thinking to do to speed up performance is to duplicate the userdata, one for native data, and the other for the translated data. When useredits their input, the native data is shown. When the application isshowing the data in a page, the translated data is shown.My question is, would it make a performance difference if I store the nativedata and the translated data in the same table, or would it be better tostore the cached data in another table?
I have combined three reports into one big report. I would like to someway cache the big report, and then create little reports from the cached report. What would be the best way to go about doing this?
Hi, We have 3 tables in sql server for simillar information about 3 different countries. some times I select from all countries so I need to use all tables and some times just one country so I select from one table.
I want to know that is it good to combine these three tables into one and add one field to define the country name? Which way is better, my own way or this new one? Please let me know the advantages and disadvantages of each of these ways.
I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:
CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:
Select c.CompanyID, c.CompanyName, a.AddressName
FROM Company c
LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID
LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID
WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))
There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.
Is there any way to optimize this query, or is it a problem with the base table implementation?
I have 2 identical DB on the same server with the same indexes on both db, but when I run a query using query analyzer, on db A it will take 6 sec but run the same query on db B it will take 1 minute. I checked the indexes on all tables in 2 db's are the same.
I used show execution plan, it does show that it is not using some indexes on the 2nd db, I droped all indexes and rebuild them again, but the same result.
Any idea why it is taking so long on the other db.
