Dear friends
We have one problem in our existing system.We are expecting some expert comment on this.We have one corebanking system back end as MS SQL server with IIS server.Our system is always very slow in the peak times of tranasactions.We are planning to optimize this with a short time plan .So pls give some suggestions that our DBA team can implement in a short time with SQL SERVER 2000
Dear friends We have one problem in our existing system.We are expecting some expert comment on this.We have one corebanking system back end as MS SQL server with IIS server.Our system is always very slow in the peak times of tranasactions.We are planning to optimize this with a short time plan .So pls give some suggestions that our DBA team can implement in a short time with SQL SERVER 2000
Iam using the below query to create a key using the column combinations and a seperator"%" to insert the same in to table my issue is with the perfomance of this query. This query is returing aroung 6000 records and its taking 11 seconds to return the result.is there any way that i can optimize this query to improve perfomance.Select (Item.ItemCode +'%'+ Product.Name + '%' + Quantity.ID) as Key1 from Items,Products,QualityPlease advice
I have a table with almost 2 mil records. There are two columns (Col1 int, Col2 int). Combination of them is unique. When i run a simple select statement like (select * from tblTableName Where Col1 = 5) with execution plan, cost is extremely high, even with PK(col1+col2). I tried to put clustered index on those columns but execution plan still shows "table scan" instead of "index seek". How can I speed this up ? I use that table mostly for search.
I look after a database which is part of a third party CRM product. Theusers of the product complain of intermittant poor performance, thesuspicion is that some more senior users are running their own queries(the product allows users to do this). I've been asked by thedevelopment team to try to capture the details of long running queries.I've looked at the events listed in profiler and can't see one thatwould be useful. Ideally I want to know who is running which query thatis taking longer than x seconds.Any suggestionsTIALaurence
;with cte as ( select rank() over (partition by username order by guid ) as rank from MyTable where siteurl='myurl' and VisitedDateTime between '2007.02.05' and '2007.09.30' and IsFiltered=0 group by username,guid )
select count(rank) from cte where rank=2
this query is taking 6 seconds to execute .. 'MyTable' table is properly indexed.. 1 million rows are returned by common table expression.. i want to reduce execution time of this query to some milli seconds..please help me..
In a database here we have a table that is a list of codes. Many other tables in our database have foreign keys to this table (T_TYPE_CODE). The joined column is an integer that is a clustered index on the table.
One of the developers here says that in one of his queries he has to OUTER JOIN to this table 15 times and that the OUTER JOIN is killing performance. He wants to add a record to T_TYPE_CODE that will represent NULL so that any NULL values in the tables that foreign key to this table will use that ID instead of NULL. In this way he could use all INNER JOINs.
To me this seems like a bad idea - NULL is NULL and creating a value to represent NULL will open a whole can of worms.
My question: Is there a performance hit for using OUTER JOINs against this table, considering that the join is on a single column and is a clustered, unique index?
Also, what problems can we expect to run into if we use a
Can someone tell me if it is possible to see the drives on the server using Perfomance Monitor? I so, where are tehy hiding because i struggled the wholed day!
We recently upgraded from sql 2000 to sql server 2005. Our system was developed using Microsoft visual Basic. Since we upgraded to sql server 2005 our system has been very slow. I suspect that it was because of the new sql 2005 installation that i made.Does anyone know how to solve this problem for me to be able to increase the perfomance speeed of our system again. It has been stressing me for a while now.
In my cube, I used Time Intelligence wizard to create calculated members such as YearToYearGrowth and YearToYear Growth%. But when I try to pull those two items in RS 2005 for more detailed levels, the performance of running MDX dataset is terrible even though my fact table has only 36234 rows of data and I have 6 dimensions (the largest dimension includes 37801 rows).
One thing that I did was removing ALLMEMBERS keywords in the MDX designer but I didn€™t improve much the time it takes to execute a query. Right now, it takes 1.5 minutes to execute may MDX query in RS 2005. Is there a way to improve the performance of MDX query (generated in RS) in RS 2005?
i have a table with 5 million rows (6 months data) .. every month approximately 1 million data is getting inserted..(not bulk insert) ..table is properly indexed.. There is a stored procedure to generate report based on this table.. (only this table no joins) .. stored procedure do lot of permutations (includes lot of temporary tables ,group by having,count() etc).. it takes 2 minutes to generate the report.. i want the report to be generated with out taking a second..
should i partition this table??? size of table is 2gb.. i dont know whether this table is a right candidate for partitioning or not.. please help me..
Dear ReaderI am trying to design a database. How can I make best Judgement that Indexing (which I am trying to fix during Diagram Desingning process)is ok.I am able to identify the best candidate for the indexing.Below is the details I want to understand:AreaZIPCityCountyDistrictState/ProvinceCountryNow I want the data retrival optimization through Index. (you can suggest another idea, also)Entities Area,...., Country have independent tables.Example:Area_TableAreaID (PK)AreaThey have relationship- one to many- if you go from Country to Area.There is one more table:Location_Table (PK)LocationIDAreaIDZIPIDCityIDCountyIDDistrictIDState/ProvinceIDCountryID(Location_ID is further related to the Address of the contact.)GUI has a single form to enter these details.On a save command details in all the tables -Area to Country- (individually) being inserted.& simultaniously Location_Table is also being inserted with the details.Following is the situation of being queried these tables:(1) GUI user can select an Area than the related details of ZIP .., ..., ...upto Country etc. should be loaded automatically (id it is previously stored by the user entry in the database.)(2) Contacts have to be retrived on the basis of Area, ZIP, .....County. (Necessary Groupings are required )Example:If Contacts are queried Country Wise then the Display should beCountry1State1District1County1City1ZIP1Area1Area2ZIP2City2County2District2Country2Please Guide.SuryaPrakash****************************************** This message was posted via http://www.sqlmonster.com** Report spam or abuse by clicking the following URL:* http://www.sqlmonster.com/Uwe/Abuse...0255a1765491f15*****************************************
(From an exchange originally posted on SQLServer.com, which wasn't resolved...)
To return views tailored to the user, I have a simple users table that holds user IDs, view names, parameter names, and parameter values that are fetched based on SUSER_SNAME(). The UDF is called MyParam, and takes as string arguments, the name of the view in use, and a parameter name. (The view the user sees is really a call to a corresponding table returning UDF, which accepts some parameters corresponding to the user.)
But the performance is very dependent on the nature of the function call. Here are two samples and the numbers reported by (my first use of) the performance monitor: Call to table returning UDF, using local variables:
declare @orgauth varchar(50) set @orgauth = dbo.MyParam('DeptAwards', 'OrgAuth') declare @since datetime set @since = DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP) select * from deptAwardsfn(@orgauth,@since)
[187 CPU, 16103 Reads, 187 Duration]
Call to same table returning UDF, using scalar UDFs in parameters:
SELECT * from deptAwardsFn ( dbo.MyParam('DeptAwards', 'OrgAuth') ,DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP) ) [20625 CPU, 1709010 Reads, 20632 Duration] (My BOL documentation claims the CPU is in milliseconds and the Duration is in microseconds -- which I question.) Regardless of the unit of measure, it takes a whole bunch longer in the second case.
My only guess is that T-SQL is deciding that the parameter values (returned by dbo.MyParam) are nondeterministic, and continually reevaluates them somehow or other. (What ever happened to call by value?)
Can anyone shed some light on this strange (to me) behavior?
----- (and later, from me)--- (I have since discovered that the reference to CURRENT_TIMESTAMP in the function argument is the cause, but I suspect that is an error -- it should only capture the value of CURRENT_TIMESTAMP once, when making the function call IMHO.)
-- Get the new Customer Identifier, return as OUTPUT param SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned -- users. IF @FK_UserIDList IS NOT NULL EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record -- Retrieve Address reference into @AddressId -- EXEC spInsertForUserNote -- @FK_UserID, --@NoteID, -- @BeenRead -- @Fax, -- @PKId, -- @AddressId OUTPUT
COMMIT TRANSACTION
-------------------------------------------------- GO
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1 FROM testsupplierSCNCR I am getting the result of 01/01/2007
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows. SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches FROM dbo.MAKES INNER JOIN dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC "> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" /> <asp:Parameter Name="ATT_ID2" /> <asp:Parameter Name="VAL2" /> <asp:Parameter Name="ATT_ID3" /> <asp:Parameter Name="VAL3" /> <asp:Parameter Name="ATT_ID4" /> <asp:Parameter Name="VAL4" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID"> </asp:DropDownList> <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br /> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False" ReadOnly="True" SortExpression="DERIVATIVE_ID" /> <asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" /> </Columns> </asp:GridView> </asp:Content> AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback). Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage. Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in ** =================================== Sequence contains no elements ------------------------------ Program Location: Â Â at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke() Â Â at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120)) insert into #temptable SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10 --column name declare @cname varchar(30)
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!! -- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on go if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1 if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name FROM... WHERE ... and calcd name in (@variable)
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
End Sub /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// I executed the code successfully and I got a box which asked for "Enter the query string". I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says. Command(s) completed successfully. What I really need it to do is to display the results of the query. Does anyone know how to do this? declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId