I've a complex view on a SQL 2014 Enterprise Edition. If I query the view with:
SELECT * FROM myComplexView
it takes 14 seconds to completes
if I want a subset of the result and I run the query with a WHERE clause:
SELECT * FROM myComplexView WHERE [Season]='A16'
The query never completes (I've waited 10 minutes and then cancelled the task).
THis is so annoying. I have 3 ADO executes in my program. THe first one creates a view, the second one performs an outer join on that view and returns a result set, the third execute drops the aforementioned view. THe program that is using this is installed on about 200 computers scattered across Germany and Italy, all querying the same MSsql server 7.0. THe queries run quite quick when few users are actively using the program (after hours for example). however in the heat of the day performance goes up and down dramatically with identical queries taking from 1 to 20 seconds to return their result set. Now I initially thought 'bandwidth issue out of our server'. However I noticed that if I take those three queries and run them from the sql server enterprise manager( running on the same computer as the aforementioned program) then the queries run instantly and the data is in my result pane in less than 2 seconds ALWAYS....even when the program is dogging it with 20 second delays before the result set returns. I know it is hanging on the return of the result set as I put a stop after before each ADO execute in order to check which one was eating up my time. Why is there this dichotomy between running the queries from my enterprise manager versus running them from an ADO object. Both are using TCP/IP (no named pipes involved). I havent monkied with the attributes of the ADO result set so they are all set to default.
I have used the sql server profiler to trace these queries and they always run in less than 33 milliseconds. THe duration is also never more than 33 milliseconds. THis stinks of a network resource issue but what always leads me somewhere else is how consistent the performance of the enterprise manager is when it runs the exact same three queries.
Here is my slightly edited connection string
Public Const connection_string = "Provider=SQLOLEDB;Server=000.000.000.000;" & _
"User ID=johndoe;Password=janedoe;Network=dbmssocn;" & _
"database=fidojoe"
Here are the 3 ADO executes:
conn.Execute (sqlstr_create_view)
Set resultset1 = conn.Execute(sqlstr_get_providers_by_DMISID)
conn.Execute (sqlstr_drop_view)
I have the below query which returns thousands of records. can I optimize the returned result set faster without changing the structure of the database?
SELECT dbo.tblComponent.ComponentID, dbo.tblComponent.ComponentName, dbo.tblErrorLog.ShortErrorMessage, dbo.tblErrorLog.LongErrorMessage, dbo.tblErrorLog.LogDate, dbo.tblErrorLevel.Description,dbo.tblErrorLog.ErrorLogIDFROM dbo.tblErrorLevel INNER JOIN dbo.tblErrorLog ON dbo.tblErrorLevel.ErrorLevelID = dbo.tblErrorLog.ErrorLevelID INNER JOIN dbo.tblComponent ON dbo.tblErrorLog.ComponentID = dbo.tblComponent.ComponentID
Thanks.
I have following common table expression query which is taking like 15 hours to run. would someone suggest what can I do to speed this thing up..
; with
a as (select proj_id, proj_start_dt,proj_end_dt, case when charindex('.', Proj_ID) > 0 then left(Proj_ID, len(Proj_ID) - charindex('.', reverse(Proj_ID))) end as Parent_Proj_ID from ods32.dbo.Proj a), --add Parent_Proj_ID column
b as (select proj_id, proj_start_dt,proj_end_dt,Parent_Proj_ID from a where PROJ_START_DT is not null and PROJ_END_DT is not null --get all valid rows
union all
select a.Proj_Id, b.PROJ_START_DT, b.PROJ_END_DT, a.Parent_Proj_ID from b inner join a on b.Proj_Id = a.Parent_Proj_ID where a.PROJ_START_DT is null or a.PROJ_END_DT is null) --get all invalid children of valid rows and give them the dates of their parents
update a set PROJ_START_DT = b.PROJ_START_DT, PROJ_END_DT = b.PROJ_END_DT
from WPData a left outer join b on a.Proj_ID = b.Proj_ID -- join up and update
thanks
Back in the days of SQL 7.0 I used a lot of ODBC SELECT querying form VB applications, in which I implemented NOLOCK in order to prevent the primary business applications from being locked out of tables once the queries were run.
Now, quite a few years later, I'm busying myself converting a lot of old Access based forms and queries to TSQL on SQL-Server 2000, and wonder aimlessly why NOLOCK queries (simple select ones) are imensely slower than a standars select clause.
SELECT * FROM employees
This would be much much faster than the code below, but users would get "The current record could not be accessed, as it is being used by another user", evidently because I'm locking the record while producing the output.
SELECT * FROM employees (nolock)
So this could should - as I remember it - do a dirty read on table, not obstructing other users and give me a snapshot of date as they are, although they might be locked for edit.
Could anyone explain to me why the nOLOCK query fials to give me any output? It is as if the nolock request is waiting for the table/records to free? In which case I'll never be able to run a query.
Cheers in advance, Trin
USE GLPDEMO
GO
select t.name as TriggerName, ta.name as TableName, o.parent_obj
into GLPDemo.dbo.Temp_TablesAndTriggers
from sysobjects o inner join sys.triggers t
on t.object_id = o.id inner join syscomments c
on c.id = t.object_id inner join sys.tables ta
on ta.object_id = o.parent_obj
where xtype = 'tr' and c.text like '%Audit%'
DECLARE @DBTrigger as varchar(100), @DBTable as varchar(100), @exestr as varchar(100)
DECLARE TCursor CURSOR for
SELECT TriggerName, TableName from Temp_TablesAndTriggers
OPEN TCursor
FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable
WHILE @@FETCH_STATUS = 0
select @exestr = ' DISABLE TRIGGER GLPDemo.dbo.' + @DBTrigger + ' ON GLPDemo.dbo.' + @DBTable
EXECUTE sp_executesql @exestr;
FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable
CLOSE TCursor
DEALLOCATE TCursor
--DROP TABLE #Temp_TablesAndTriggers
Hi,
I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!!
What's going on?
Any thoughts highly appreciated.
t.i.a.,ratjetoes.
Hi all,
I know this sounds rather dumb but my select statement is running forever!
I am trying to execute the statement through my C# code.
If I try to run the query through sql server management studio, sometimes it runs fine, but sometimes it keeps running and never returns a value.
I am doing something like this:
###########################################################################################
String query = "SELECT studentID from StudentTable WHERE studentDeptID = '100' AND deptName = 'CS'";
SqlCommand command = new SqlCommand(query, connection);
Object myObject = command.ExecuteScalar();
############################################################################################
StudentTable contains roughly 1000 rows.
Somebody please help me out of this. Thanks in advance.
Surya
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies View RelatedHi,
I am creating a stored procedure which takes a few paramaters. One of which is a string of comma separated codes. At the end of my select query the codes will be used in the where clause.
What I'm doing is parsing the comma separated string and populating a temp table called #codes. So my query will look something like this:
select * from tableA
where tableA.col1 = 'something'
and tableA.code in (select * from #codes)
and....
However, the code paramater can be null and if this is the case I want the query to be called for all codes - i.e. I effectively want to remove the and tableA.code in (select * from #codes) part of the where clause.
Is there any clever way of doin this other than having a if... else... and writing 2 queries - one with the and and one without it?
Hope this is clear.
Thanks,
Wallace
Hello all,
I've got some problem with the following query:
select
subscriber_id, sum(convert(int,n_inboundcalls)) as totalinbound
from calldiversityratedaily
where totalInbound < 10
group by subscriber_id
The error message provided is:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'totalInbound'.
I'm using MSSQL server 2000, version 8.00.194 and it appears that I can't use the name I give to the sum in the where clause.
Could anyone help me please?
My table is as follows and I have a string ‘U2’,’U4’
CustomerID UserID OtherUsers
C1U1#U3~#U5~
C2U2
C3U3
C4U4#U1~#U2~
C5U5
C6U1#U2~#U5~
C7U2
C8U3#U1~#U4~
Now I should issue a sql which should extract the CustomerID where UserID in (‘U2’,’U4’) or (U2 or U4) in OtherUsers.
i.e I should get the CustomerIDs C2,C4,C6,C8
I have trouble in coming out with the sql. Does anyone have idea how to achieve this?
create function stzipcustinfo
( @campaign varchar(50), @startdate datetime, @enddate datetime,@audience_type char(10) )
RETURNS TABLE
AS
RETURN
(Select distinct t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,
t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone,sum(t.totalquantity) as Totalquantity,
t.Audience_type
from
(
select distinct
v.sopnumbe, v.campaign, v.custnmbr, v.custname, v.address1, v.address2,v.city,v.state,v.country,v.zipcode,
v.Phone, v.totalquantity as totalquantity,
case @audience_type
when v.custclas then v.custclas
end as audience_type
from vwstzipcustaudienceinfo v
Where (v.itemnmbr = '' or v.itemnmbr=v.itemnmbr) and v.Campaign = @Campaign and (v.docdate between @startdate and @enddate)
) as t
where t.audience_type is not null
group by t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,
t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone, t.Audience_type
)
--select * from mystatezipcustaudienceinfo('cpd','1/1/2008','5/15/2008','INDPATIENT') --getting 500rows
--select * from mystatezipcustaudienceinfo('Cpd'','1/1/2008','5/15/2008','INST-HOSPITAL') -- note getting single row
problem have a “-“ in them..i m not getting result..so what condition should be in red color one..
thanks
I do have a table type as input
---------------------------
num|columnname|value
---------------------------
1|Name|3X5900
2|employeeID|null
3|JOD|null
4|Address|null
From this I have to form the where clause like "Name like'3X5900' ".This will be dynamic , as the search may vary on the next call for search.
How I have to implement this dynamic query in the below mentioned code.I have add the dynamic query where i have mentioned as '---- Where clause has to be added '. Sometime the table input will be having all the value for search. How I can implement this in my query with good performance.
DECLARE @Where varchar(4000);
Select @where = 'v.Name LIKE ''3X5900'''
SELECT * from
(SELECT Row_number()Over(Order By V.ProjectId) Rownum,
v.Firstname,
[Code] ....
How can update / insert records from table1 colum to table2 colum
with condition ?
I want to insert/update records like this.
update employee
set joine_date = select joine_date from master_table
where emp_id.employee=emp_id.master_table
regards
Martin
Hi,
hope someone can help or suggest something to help me with the issue.
I have a list of projects. this list contains all master (parent) and all subprojects(child). when I click on a project I want to be able to retreive information about that project and it's subprojects. here is my delema. I want my sql query to check if project is master or sub. if master then get all data for this project and its subprojects but if it's sub then get data only for that sub. below is a sample data that I hope it clear things up
parent_ID child_id Type projname
-----------------------------------------------
100 100 P parent_X_proj
100 25 C child_X_proj
100 29 C child_X_proj2
200 200 P parent_Z
300 300 P parent_Y
etc................
this is how my table is constructed. my application passes child_id and what I want is if someone clicks on parent_X_proj I want to be able to retreive the three projects (100,25,29) but if someone clicks on child project (29) then I want only that project.
so I want my query to look for the type and if my type is P (parent) then get all project where parent_id = 100(for example) but if type= C then get child_id = 29.
I know it could be done in stored procedure buy my application cannon executre SP but only sql statements.
Thank you for any help
SQL Server 2012 Performance Dashboard Main advices me this:
Since the application is from a vendor and I have no control over its code, how can improve this sitation?
Hi all,
i have a query as below that creates a count in the field Total. I wanted to then be able to say only show me where there are more than 2 incidents. But i can't get it to recognise my created field. Any advice?
Thanks.
select a.vchcompanyname, count(*) as total
from company a inner join incident b
on b.iownerid = a.icompanyid
where b.iincidentcategory = 1
and a.icompanytypecode = 102165
--and total > 2 (Wont recognise Total)
group by b.iownerid, a.icompanyid, a.vchcompanyname
order by 2 desc
First I have to apologize about posting the problem here , since it not actually relate to SQL Server
I am really new to database programming.
I use ADO (the ActiveX one , Not ADO.NET) In an MFC Application to access MS ACCESS Database
I can insert date in to table using statement like this
insert into tableXXX (date_field) values ('1/2/2007')
but when i tried to query it using this statement
select * from tableXXX where date_field = #1/2/2007#
Nothing return
I also tried to use #1-2-2007# , #01-02-2007# but it all the same
Can someone tell me how to successfuly use date as a query condition ?
Thank in Advance
Hi all,
I have to write a select query which need some logic to be implemented.
Query is like
select name,number,active,
if active ="true" then
select @days=days from tbl_shdsheet
else
@days=''
end
from tbl_emp
In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet
So how can i write queery for this.
Hi, I was wondering if someone could help out. I need to create a stored procedure, but before i do so, I need to know if I can store a conditional expression in a string and just use that variable as my condition for the WHERE clause. The reason I ask this is because I am trying to create a stored procedure that queries different things depending on what the inputs are.
View 2 Replies View RelatedI have one table which contines 3 columns they are
1.emp
2.monthyear
3.amount
in that table i am having 6 rows like below.
emp monthyear amount
1 102013 1000
2 102013 1000
1 112013 1000
1 112013 1000
2 122013 1000
2 122013 0000
i want a total on group by condition on each employee. which will have the data from NOV to dec 2013 only.
out put should be like this
1 2000
2 1000
I wish to make a query with if condition Implemented in a database sql server 2008 R2.
I would like to set up a system FEFO (first expired first out) based on batch number based on the dates of Lapsed but since I struggle to put my request in place. The principle of my request is:
if amount of movement (QTEMVT)> = amount entered by the user via a user interface then withdraws the amount entered by the user in the batch (NUMLOT) the amount of movement of the item that lapses the first (execution of my request).
else if amount of movement (QTEMVT) <amount entered by the user
then the difference between the amount entered by the user and the amount of movement (QTEMVT) (execution of my request) and the following conditions:
the amount of movement (QTEMVT) = the amount of movement (QTEMVT) of this item stored in my database and the amount of movement (QTEMVT) <> 0
by taking the difference of the item requested directly from the batch (NUMLOT) of the item directly after lapses.
Basically I want to set up an item management query based on batch number and expiration dates.
ps: QTEMVT = quantity of the item stored in my database
NUMLOT = batch number items
DATFABRIC = manufacturing date items
DATPEREMP = expiry date items
Here is my request:
SELECT f.NUMLOT,f.DATFABRIC,f.DATPEREMP,f.QTEMVT
FROM FAIRE f INNER JOIN mouvement m ON f.CODMVT = m.CODMVT
INNER JOIN TYPE_MOUVEMENT tm ON tm.CODTYPMVT = m.CODTYPMVT
INNER JOIN SOUS_SITE ss ON ss.CODSOUSIT = m.CODSOUSIT
INNER JOIN SITE s ON s.CODSITE = ss.CODSITE
[Code] ....
Is it possible to stop a query from running if a condition is not met?
I have a stored procedure that returns some email addresses. A condition is that a load of new data should have happened in the current month.
If a load of new data hasn't happened I want the stored procedure to stop and return an error message instead of returning the email addresses.
I have a variable that finds the maximum data load date and I thought I could use an IF... ELSE... to check if new data had been loaded. Like this:
DECLARE @MaxLoadDate as date = (select max(load_date)
from #table)
IF @MaxLoadDate<Dateadd(month, Datediff(month, 0, Getdate()), 0) --First Day of Current Month
<Something here to kill the query>
ELSE
SELECT email_address
FROM... etc
I've tried a couple of things like PRINT 'Error', and SET NO EXEC ON, but the query seems to happily carry on past the IF condition and return the email addresses. I know that data hasn't been loaded this month, so it should fail.
following is a working code
declare @dte as datetime='2015-04-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);
[code]...
iam getting the ouput correctly my requirement is i want to write a condition here
JOIN busdetails b ON m.bus_id = b.bus_id
i want to write this statement as
JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'
when i tried this code iam getting error
Good day,
The following query performs acceptably (2 seconds against 126,000,000 rows in the main table):
SELECT Count(*)
FROM
Message1_2_3 INNER JOIN
VDMVDO ON Message1_2_3.VDMVDO_ID = VDMVDO.VDMVDO_ID INNER JOIN
NMEA ON VDMVDO.NMEA_ID = NMEA.NMEA_ID
WHERE
NMEA.NMEA_ID BETWEEN 14000000 AND 14086000 AND
VDMVDO.RepeatIndicator = 0 AND
NMEA.SentenceFormatterID = 'VDM'
When we change the first condition from an Int column to a DateTime as in:
NMEA.TimeDate BETWEEN CONVERT(DATETIME, '2007-07-09 8:30:00', 102) AND CONVERT(DATETIME, '2007-07-09 9:30:00', 102)
the query performance falls to 14 seconds, even though both columns are indexed and a similar number of rows are found. When the select clause changes from a simple Count to a complex Max expression, response time falls to over a minute!
Any thoughs on optimizing the DateTime search would be greatly appreciated...
I have a query where i would calculate the counts of three ServersÂ
declare @Managed float
declare @Active float
DECLARE @Values varchar(1000)
SET @Values = 'WE,EE,CO'
select @Managed = count(Name0) from v_R_System where Operating_System_Name_and0 like '%Workstation%'Â
[Code] ...
Here i need output like below
Workgroup Managed Active
'WE' 255 ,400
'EE' 300 ,450
'CO' 155, 600
So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?
For example..
select * from mytable where MyNum = 7
If this brings back more than 1 row, I want to display a message that says,
Print 'There is more than one row returned'
Else (If only 1 row returned), I don't want to print anything.
Can I do this? Thx!
Dear friends,
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
What is wrong??
Regards,
Pedro
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS",   otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST",   otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAMEÂ CREATED"),
IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")
Following is a working code
declare @dte as datetime='2015-04-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);
create table #bus_master(bus_id int,bus_name varchar(50),uname varchar(50))
insert into #bus_master values(100,'A','lekshmi')
[Code] ...
I am getting the output correctly. My requirement is i want to write a condition here
JOIN busdetails b ON m.bus_id = b.bus_id
I want to write this statement as
JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'
When I tried this code I am getting error.
I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052 Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
how to achieve this in single query?