Hello,
Can any one suggest me in optimizing the SP.
To execute single SP it takes nearly 50 seconds.
Can i know what are ways to optimize the SP.
Thanks
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
this is the source error for this error
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
please any one can help me?
I want move data from informix to SQL server 7.0 every 10 seconds. I do not know what is the best way. It seems that I need create a DTS package and schdule the task from SQL server Agent, but can the task be schduled to reoccurred every 10 seconds?
Thanks in advanced.
Yu
I have to made a change and want to see it this is possible. The column IDPRT# is used here as MAX. THe user wants to exclude a few of the items which skews the average. Can i also select the IDPRT# a second time? and then in the CRystal reports I can select not =? but will this mess up the MAX line in any way?
SELECT "OEIND94"."IDDOCD" AS INV_DATE,
"OEIND94"."IDORD#" AS ORD_NUM,
"OEIND94"."IDORDT" AS ORD_TYPE,
"OEIND94"."IDPRLC" AS PROD_FAMILY,
"OEIND94"."IDPR$C" AS PRICE_CODE,
"OEIND94"."IDCOM#",
MAX("OEIND94"."IDPRT#") AS ITEM_REF,
"ICPRT1"."IARCC4" AS PROD_TYPE,
[code]....
Hi All,
My week starts on Monday rather than on Sunday which is default(US, English) in SQL Server and would like to change the same so I would get proper weeknumber and dayindex using DATEPART.
I am looking at different ways of setting DATEFIRST(SET DATEFIRST 1) in SQL Server 2005.
I could set in a stored procedure, but this isn't a feasible way for me because I am using .nettiers to generate by business objects and stored procedures. I need to alter the sp's everytime I newly generate the code and sp's.
I couldn't set it in a function which I was hoping initially. I understand this datefirst is stored in one of the sys table in MASTER DB and I couldn't find a straight forward way to change this.
Can anyone suggest me a way to set the DATEFIRST either at a database level or at a server level(probably by changing the sys table in MASTER DB).
Any help on this would be greatly appreciated.
Ponnu
Trellisys.net
How many ways are there to connect to a database in ASP.NET? Could someone list them in a 1,2,3... manner.
For those who are going to ask why I want to know this and why I don't do one way, I'll explain after I get the answer.
I have I rather complex query and need to display a Sum in different ways. Now I'm wondering if the performance improves if you nest the queries in the described way. To me it looks, as if the sum just has to be calculated once?
select SUM(tiempo) as time_minutes,
CONVERT(varchar(5), dateadd (minute,sum(tiempo),'1900-1-1 0:00'), 114) as time_hours,
SUM(time)* 0.95 as time_discount
from table
Select tiempo as time_minutes,
CONVERT(varchar(5), dateadd (minute,tiempo,'1900-1-1 0:00'), 114) as time_hours,
tiempo * 0.95 as time_discount
From (
Select Sum(tiempo) as tiempo
from table)a
What is the difference between these 3 functions:
getdate()
{fn now()}
current_timestamp()
I'm trying to simplify a SQL Stored Procedure.The query accepts an int, @ItemTypeID intI have the Query:SELECT ... FROM ItemList WHERE ItemTypeID = @ItemTypeIDor, if @ItemTypeID is 0,SELECT ... FROM ItemList Is there a way to do this query without doing:IF @ItemTypeID = 0BEGIN ...SELECT QUERY...ENDELSEBEGIN ...SELECT QUERY...END?
View 5 Replies View RelatedHey - I am trying to create a procedure that will retrieve information from multiple SQL Servers. For example, I want to use a select statement on a systems table. Instead of running it from each server, I would rather run it from one and get info on all the servers. To do this, I know that i can 'link' a Server and then make select calls to that server. Some of the other administrators have expressed concern with this. Are there issues that I should research before linking servers. Is there a better way to do what I want to do? Any Input would be appreciated.
View 2 Replies View RelatedFor some reason my SQL Server slows down throughout the day for no apparent reason..
The database is 100 gig transaction replicated to 2 data warehouses.
Ive done the usual stuff like monitoring processes killing off blocking transactions but it doesn't seem to do the trick.
There are no jobs running at the point of slow-down so at this current time I am a little lost.
Is there some way to determine why these slow-downs are happening ?
The system becomes completely un-responsive during these periods !!
HELP !! :eek:
Please tell me the 4 ways to know the record count of a table?
View 14 Replies View RelatedHi everyone.
I need to know which are the best ways to connect to a remote SQL SERVER 2000 from inside a VB6 application. By remote I mean on a dedicated server outside my LAN that I can access by IP address.
Any opinion would be appreciated.
Dear All,
i've tried with indexed views, but because the view is referenceing another view, i was unable to create a clustered index on that view.
so please let me know how can i improve the performance of the view.
thank you very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
How many ways we can link a remote server?
I know we can do using linkedserver . Is there any others to do?
Hi
can anybody tell how to compare two databases on sql server
thank u
vizai
Hi all,
I am just wondering is there any other ways to execute an integration service package other than using sql server agent, dos command and manually execute the package in BIDS? I am thinking of running the package on web, is this possible?
What I am trying to do is let user to run the integration service when they need to, so instead using SQL Server Management Studio to run the task being set in SQL Server Agent, I am thinking of something where user can log into a website (like the report server) and then run the integration, so that the data for the report server's reports are being update. Please help out if any know a solution to it. Thanks in advance.
Daren
I want to know that how many ways we have to count the data in sql.
I know only one way..that is. using count keyword.. are their any ways to find out the other ways..
hello friends,
I need to transfer my database from one server to database on another server every 24 hours. I can create windows application but it will be cumbersome to write bulk of code . So can u suggest me some service or any other way through query or stored procedure or by job scheduling which can run every 24 hours and move my data from one database on one server to another sql database. Both database systems are sql server 2000 but servers are diffeerent so how to connect them while transferring dbs. ? Any help is appreciated.
regards,
max
Hello.I would like to learn all the possible ways that we can access and modify data in ASP .net 2.0 programmatically.for example one way would be like this: StringBuilder sql = new StringBuilder();sql.Append(" SELECT *"); //count the total number of recordssql.Append(" FROM dbo.tblJobTitle "); //get the connection string from web servicesstring strConnection = new sqlconnection.SQLConnection().GetSQLConnectString(sqlconnection.SQLDSN.SCIC);//Use the Microsoft.practices SqlDatabase object to execute our sql.SqlDatabase SqlHelper = new SqlDatabase(strConnection);SqlDataReader reader = (SqlDataReader)SqlHelper.ExecuteReader(CommandType.Text, sql.ToString());the i can loop through reader and get the data OR I can create a DataSet like this: SqlDatabase db = new SqlDatabase(strConnection);
DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());
DataSet dst = db.ExecuteDataSet(dbCommand);I want to know if there is a comprehensive book explainaning all these possible ways to access and modify data using C# under ASP .net 2.0I really really apreciate this.Thank you very much.
I am learning the Optimizer from the book "Querying Microsoft SQL Server 2012" for certificate exam 70-461. I really cannot understand how it explains the number of possible ways to execute a query joining three tables. the pseudo-query is:
SELECT A.col5, SUM(C.col6) AS col6sum
FROM TableA AS A
INNER JOIN TableB AS B
ON A.col1 = B.col1
INNER JOIN TableC AS C
ON B.col2 = c.col2
WHERE A.col3 = constant 1
AND B.col4 = constant2
GROUP BY A.col5;
The book says:"Start with the FROM part. Which tables should SQL Server join first, TableA and TableB or TableB and TableC? And in each join, which of the two tables joined should be the left and which one the right table? The number of all possibilities is six, if the two joins are evaluated linearly, one after another."
Q1: How could it be six possibilities? From my understanding, lets say, if the SQL Server has to join A and B first, and then join C, in this case I can think of 4 possibilities, which are:
1. When A Join B, Left: A, Right: B.
When Join C, Left: result of A join B, Right: C
2. When A Join B, nbsp;
When Join C, nbsp;When A Join B, nbsp;
When Join C, nbsp;When A Join B, nbsp;
When Join C, "line-height:13.5px;">
Q2: The section following the previous question says there are 4 different types of join.."This already gives four options for each join. So far, there are 6 x 4 = 24 different options for only the FROM part of this query."
How can it be 6 x 4? My understanding is 4 is only for 1 join, but in our case, there are 2 joins, so it should be 6 x 4 x 4.
Hi guys , is there any ways/suggestions for strengthen up the security for SQL server 2005 ? Due to several attacks from unknown places to my database's server , so I would like to get a way for increase the SQL security. Hope able to gather some info from web as well. Thx a lot guys.
Best Regards,
Hans
I have a table (F_POLICY_TRANSACTION).This table has a couple of million rows in it.I am using a column named POLICY_TRANSACTION_BKEY to select records to delete (approximately 750k using the code below)This column has a non-clustered index applied..This is the code I have used:
WHILE 1 = 1
BEGIN
DELETE TOP(50000)
FROM F_POLICY_TRANSACTION with (tablockx)
[code]....
Problem is, it takes around 10 minutes to run.Is there any way it can be made more efficient?I have tried varying the rowcount with no success
SQL Mobile database seems to not provide import/export utilities.
I think using Publication/Subscription is one of the solution, is it right?
Also, besides typing insert statement manually, there are any other ways to transform data to the SQL Mobile database?
Hi.I have a sql: 1 SELECT ForumID, Title, Description,2 (SELECT PostID3 FROM (SELECT TOP (1) PostID4 FROM Forum_Posts5 WHERE (ForumID = Forum_Forums.ForumID)6 ORDER BY PostedDate DESC) AS derivedtbl_1) AS LastPostID,7
8
9 (SELECT Title10 FROM (SELECT TOP (1) Title11 FROM Forum_Posts12 WHERE (ForumID = Forum_Forums.ForumID)13 ORDER BY PostedDate DESC) AS derivedtbl_2) AS LastPostTitle,14
15 (SELECT PostedBy16 FROM (SELECT TOP (1) PostedBy17 FROM Forum_Posts18 WHERE (ForumID = Forum_Forums.ForumID)19 ORDER BY PostedDate DESC) AS derivedtbl_3) AS PostedBy20 FROM Forum_Forums21 WHERE (BoardID = @BoardID)I must use this code 3 times: SELECT TOP (1) PostID / Title / PostedBy FROM Forum_Posts WHERE (ForumID = Forum_Forums.ForumID)ORDER BY PostedDate DESCPlease help me optimize this.
A table fin_cutoff contains following type of data.
fin_beg_dt | fin_end_dt
------------------------------------------------
2005-12-01 00:00:00.000 | 2005-12-31 00:00:00.000
2005-10-29 00:00:00.000 | 2005-12-01 00:00:00.000
2005-10-01 00:00:00.000 | 2005-10-29 00:00:00.000
I want to extract a row where the fin_beg_dt & fin_end_dt columns entries belong to current month
Following query is written for the same:
select fin_beg_dt,
fin_end_dt
from ecr_fin_cutoff
where month(fin_beg_dt_id) = month(getdate())
andyear(fin_dt_id)= year(getdate())
and month(fin_end_dt) = month(getdate())
and year(fin_end_dt)= year(getdate())
It is giving the appropriate results.
Please help me to optimize it.
Thanks in advance.
Shrirang
I have a sql such sa below:
select
coalesce(T1.LocalNetID, T2.LocalNetID)as LocalNetID,coalesce(T1.BSCID, T2.BSCID)as BSCID,coalesce(T1.SiteID, T2.SiteID)as SiteID, coalesce(T1.CellID, T2.CellID)as CellID,
coalesce(T1.StrTime, T2.StrTime) as StrTime,
C10701 as '10701',C11601 as '11601'from
(
--Start for Opt_Cell_pBasicM of inner table--
(
select
LocalNetID,BSCID,SiteID, CellID,
StrTime,RecordDataTime,
C11601
from
Opt_Cell_pBasicM
where
((LocalNetID = 13 and BSCID = 2))
and
((RecordDataTime between '2006-01-01 00:01:00' and '2006-01-01 12:00:00'))
and
(GranularityPeriod <= 12)
)
--End for Opt_Cell_pBasicM of inner table--
as T1
full outer join
--Start for Opt_Cell_pCellRadioM of inner table--
(
select
LocalNetID,BSCID, SiteID, CellID,
StrTime,RecordDataTime,
C10701
from
Opt_Cell_pCellRadioM
where
(
((LocalNetID = 13 and BSCID = 2))
and
((RecordDataTime between '2006-01-01 00:01:00' and '2006-01-01 12:00:00'))
and
(GranularityPeriod <= 12)
)
)
--End for Opt_Cell_pCellRadioM of inner table--
as T2
on coalesce(T1.LocalNetID,NULL) = T2.LocalNetID and coalesce(T1.BSCID,NULL) = T2.BSCID and coalesce(T1.SiteID,NULL) = T2.SiteID and coalesce(T1.CellID,NULL) = T2.CellID
and coalesce(T1.RecordDataTime,NULL) = T2.RecordDataTime
--End for first join--
)
when num of the records of Opt_Cell_pBasicM and Opt_Cell_pCellRadioM
is greater than 1000000, the speed is very slow , how to optimize it ?
thks
the sql of create table is below:
CREATE TABLE [dbo].[Examp] (
[LocalNetID] [int] NOT NULL ,
[BSCID] [int] NOT NULL ,
[SiteID] [int] NOT NULL ,
[CellID] [int] NOT NULL ,
[StrTime] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[M1] [int] NULL ,
[M2] [int] NULL ,
[M3] [int] NULL ,
[M4] [int] NULL ,
[M5] [int] NULL ,
[M6] [int] NULL ,
[M7] [int] NULL ,
[M8] [int] NULL ,
[M9] [int] NULL ,
[M10] [int] NULL ,
[M11] [int] NULL ,
[M12] [int] NULL ,
[M13] [int] NULL ,
[M14] [int] NULL ,
[M15] [int] NULL ,
[M16] [int] NULL ,
[M17] [int] NULL ,
[M18] [int] NULL ,
[M19] [int] NULL ,
[M20] [int] NULL ,
[M21] [int] NULL ,
[M22] [int] NULL ,
[M23] [int] NULL ,
[M24] [int] NULL ,
[M25] [int] NULL ,
[M26] [int] NULL ,
[M27] [int] NULL ,
[M28] [int] NULL ,
[M29] [int] NULL ,
[M30] [int] NULL ,
[M31] [int] NULL ,
[M32] [int] NULL ,
[M33] [int] NULL ,
[M34] [int] NULL ,
[M35] [int] NULL ,
[M36] [int] NULL ,
[M37] [int] NULL ,
[M38] [int] NULL ,
[M39] [int] NULL ,
[M40] [int] NULL ,
[M41] [int] NULL ,
[M42] [int] NULL ,
[M43] [int] NULL ,
[M44] [int] NULL ,
[M45] [int] NULL ,
[M46] [int] NULL ,
[M47] [int] NULL ,
[M48] [int] NULL ,
[M49] [int] NULL ,
[M50] [int] NULL ,
[M51] [int] NULL ,
[M52] [int] NULL ,
[M53] [int] NULL ,
[M54] [int] NULL ,
[M55] [int] NULL ,
[M56] [int] NULL ,
[M57] [int] NULL ,
[M58] [int] NULL ,
[M59] [int] NULL ,
[M60] [int] NULL ,
[M61] [int] NULL ,
[M62] [int] NULL ,
[M63] [int] NULL ,
[M64] [int] NULL ,
[M65] [int] NULL ,
[M66] [int] NULL ,
[M67] [int] NULL ,
[M68] [int] NULL ,
[M69] [int] NULL ,
[M70] [int] NULL ,
[M71] [int] NULL ,
[M72] [int] NULL ,
[M73] [int] NULL ,
[M74] [int] NULL ,
[M75] [int] NULL ,
[M76] [int] NULL ,
[M77] [int] NULL ,
[M78] [int] NULL ,
[M79] [int] NULL ,
[M80] [int] NULL ,
[M81] [int] NULL ,
[M82] [int] NULL ,
[M83] [int] NULL ,
[M84] [int] NULL ,
[M85] [int] NULL ,
[M86] [int] NULL ,
[M87] [int] NULL ,
[M88] [int] NULL ,
[M89] [int] NULL ,
[M90] [int] NULL ,
[M91] [int] NULL ,
[M92] [int] NULL ,
[M93] [int] NULL ,
[M94] [int] NULL ,
[M95] [int] NULL ,
[M96] [int] NULL ,
[M97] [int] NULL ,
[M98] [int] NULL ,
[M99] [int] NULL ,
[M100] [int] NULL
)
and LocalNetID, BSCID, SiteID, CellID, StrTime are set index,
the sql i want to query is :
select
LocalNetID, BSCID, SiteID, CellID, StrTime,
Sum(M1) as M1,Sum(M2) as M2,Sum(M3) as M3,Sum(M4) as M4,Sum(M5) as M5,Sum(M6) as M6,Sum(M7) as M7,Sum(M8) as M8,Sum(M9) as M9,Sum(M10) as M10,Sum(M11) as M11,Sum(M12) as M12,Sum(M13) as M13,Sum(M14) as M14,Sum(M15) as M15,Sum(M16) as M16,Sum(M17) as M17,Sum(M18) as M18,Sum(M19) as M19,Sum(M20) as M20,Sum(M21) as M21,Sum(M22) as M22,Sum(M23) as M23,Sum(M24) as M24,Sum(M25) as M25,Sum(M26) as M26,Sum(M27) as M27,Sum(M28) as M28,Sum(M29) as M29,Sum(M30) as M30,Sum(M31) as M31,Sum(M32) as M32,Sum(M33) as M33,Sum(M34) as M34,Sum(M35) as M35,Sum(M36) as M36,Sum(M37) as M37,Sum(M38) as M38,Sum(M39) as M39,Sum(M40) as M40,Sum(M41) as M41,Sum(M42) as M42,Sum(M43) as M43,Sum(M44) as M44,Sum(M45) as M45,Sum(M46) as M46,Sum(M47) as M47,Sum(M48) as M48,Sum(M49) as M49,Sum(M50) as M50,Sum(M51) as M51,Sum(M52) as M52,Sum(M53) as M53,Sum(M54) as M54,Sum(M55) as M55,Sum(M56) as M56,Sum(M57) as M57,Sum(M58) as M58,Sum(M59) as M59,Sum(M60) as M60,Sum(M61) as M61,Sum(M62) as M62,Sum(M63) as M63,Sum(M64) as M64,Sum(M65) as M65,Sum(M66) as M66,Sum(M67) as M67,Sum(M68) as M68,Sum(M69) as M69,Sum(M70) as M70,Sum(M71) as M71,Sum(M72) as M72,Sum(M73) as M73,Sum(M74) as M74,Sum(M75) as M75,Sum(M76) as M76,Sum(M77) as M77,Sum(M78) as M78,Sum(M79) as M79,Sum(M80) as M80,Sum(M81) as M81,Sum(M82) as M82,Sum(M83) as M83,Sum(M84) as M84,Sum(M85) as M85,Sum(M86) as M86,Sum(M87) as M87,Sum(M88) as M88,Sum(M89) as M89,Sum(M90) as M90,Sum(M91) as M91,Sum(M92) as M92,Sum(M93) as M93,Sum(M94) as M94,Sum(M95) as M95,Sum(M96) as M96,Sum(M97) as M97,Sum(M98) as M98,Sum(M99) as M99,Sum(M100) as M100
from
(
select
LocalNetID, BSCID, SiteID, CellID, StrTime,
Sum(M1) as M1,Sum(M2) as M2,Sum(M3) as M3,Sum(M4) as M4,Sum(M5) as M5,Sum(M6) as M6,Sum(M7) as M7,Sum(M8) as M8,Sum(M9) as M9,Sum(M10) as M10,Sum(M11) as M11,Sum(M12) as M12,Sum(M13) as M13,Sum(M14) as M14,Sum(M15) as M15,Sum(M16) as M16,Sum(M17) as M17,Sum(M18) as M18,Sum(M19) as M19,Sum(M20) as M20,Sum(M21) as M21,Sum(M22) as M22,Sum(M23) as M23,Sum(M24) as M24,Sum(M25) as M25,Sum(M26) as M26,Sum(M27) as M27,Sum(M28) as M28,Sum(M29) as M29,Sum(M30) as M30,Sum(M31) as M31,Sum(M32) as M32,Sum(M33) as M33,Sum(M34) as M34,Sum(M35) as M35,Sum(M36) as M36,Sum(M37) as M37,Sum(M38) as M38,Sum(M39) as M39,Sum(M40) as M40,Sum(M41) as M41,Sum(M42) as M42,Sum(M43) as M43,Sum(M44) as M44,Sum(M45) as M45,Sum(M46) as M46,Sum(M47) as M47,Sum(M48) as M48,Sum(M49) as M49,Sum(M50) as M50,Sum(M51) as M51,Sum(M52) as M52,Sum(M53) as M53,Sum(M54) as M54,Sum(M55) as M55,Sum(M56) as M56,Sum(M57) as M57,Sum(M58) as M58,Sum(M59) as M59,Sum(M60) as M60,Sum(M61) as M61,Sum(M62) as M62,Sum(M63) as M63,Sum(M64) as M64,Sum(M65) as M65,Sum(M66) as M66,Sum(M67) as M67,Sum(M68) as M68,Sum(M69) as M69,Sum(M70) as M70,Sum(M71) as M71,Sum(M72) as M72,Sum(M73) as M73,Sum(M74) as M74,Sum(M75) as M75,Sum(M76) as M76,Sum(M77) as M77,Sum(M78) as M78,Sum(M79) as M79,Sum(M80) as M80,Sum(M81) as M81,Sum(M82) as M82,Sum(M83) as M83,Sum(M84) as M84,Sum(M85) as M85,Sum(M86) as M86,Sum(M87) as M87,Sum(M88) as M88,Sum(M89) as M89,Sum(M90) as M90,Sum(M91) as M91,Sum(M92) as M92,Sum(M93) as M93,Sum(M94) as M94,Sum(M95) as M95,Sum(M96) as M96,Sum(M97) as M97,Sum(M98) as M98,Sum(M99) as M99,Sum(M100) as M100
from
(
select
LocalNetID, BSCID, SiteID, CellID, StrTime,
Sum(M1) as M1,Sum(M2) as M2,Sum(M3) as M3,Sum(M4) as M4,Sum(M5) as M5,Sum(M6) as M6,Sum(M7) as M7,Sum(M8) as M8,Sum(M9) as M9,Sum(M10) as M10,Sum(M11) as M11,Sum(M12) as M12,Sum(M13) as M13,Sum(M14) as M14,Sum(M15) as M15,Sum(M16) as M16,Sum(M17) as M17,Sum(M18) as M18,Sum(M19) as M19,Sum(M20) as M20,Sum(M21) as M21,Sum(M22) as M22,Sum(M23) as M23,Sum(M24) as M24,Sum(M25) as M25,Sum(M26) as M26,Sum(M27) as M27,Sum(M28) as M28,Sum(M29) as M29,Sum(M30) as M30,Sum(M31) as M31,Sum(M32) as M32,Sum(M33) as M33,Sum(M34) as M34,Sum(M35) as M35,Sum(M36) as M36,Sum(M37) as M37,Sum(M38) as M38,Sum(M39) as M39,Sum(M40) as M40,Sum(M41) as M41,Sum(M42) as M42,Sum(M43) as M43,Sum(M44) as M44,Sum(M45) as M45,Sum(M46) as M46,Sum(M47) as M47,Sum(M48) as M48,Sum(M49) as M49,Sum(M50) as M50,Sum(M51) as M51,Sum(M52) as M52,Sum(M53) as M53,Sum(M54) as M54,Sum(M55) as M55,Sum(M56) as M56,Sum(M57) as M57,Sum(M58) as M58,Sum(M59) as M59,Sum(M60) as M60,Sum(M61) as M61,Sum(M62) as M62,Sum(M63) as M63,Sum(M64) as M64,Sum(M65) as M65,Sum(M66) as M66,Sum(M67) as M67,Sum(M68) as M68,Sum(M69) as M69,Sum(M70) as M70,Sum(M71) as M71,Sum(M72) as M72,Sum(M73) as M73,Sum(M74) as M74,Sum(M75) as M75,Sum(M76) as M76,Sum(M77) as M77,Sum(M78) as M78,Sum(M79) as M79,Sum(M80) as M80,Sum(M81) as M81,Sum(M82) as M82,Sum(M83) as M83,Sum(M84) as M84,Sum(M85) as M85,Sum(M86) as M86,Sum(M87) as M87,Sum(M88) as M88,Sum(M89) as M89,Sum(M90) as M90,Sum(M91) as M91,Sum(M92) as M92,Sum(M93) as M93,Sum(M94) as M94,Sum(M95) as M95,Sum(M96) as M96,Sum(M97) as M97,Sum(M98) as M98,Sum(M99) as M99,Sum(M100) as M100
from
(
select
LocalNetID, BSCID, SiteID, CellID, StrTime,
Sum(M1) as M1,Sum(M2) as M2,Sum(M3) as M3,Sum(M4) as M4,Sum(M5) as M5,Sum(M6) as M6,Sum(M7) as M7,Sum(M8) as M8,Sum(M9) as M9,Sum(M10) as M10,Sum(M11) as M11,Sum(M12) as M12,Sum(M13) as M13,Sum(M14) as M14,Sum(M15) as M15,Sum(M16) as M16,Sum(M17) as M17,Sum(M18) as M18,Sum(M19) as M19,Sum(M20) as M20,Sum(M21) as M21,Sum(M22) as M22,Sum(M23) as M23,Sum(M24) as M24,Sum(M25) as M25,Sum(M26) as M26,Sum(M27) as M27,Sum(M28) as M28,Sum(M29) as M29,Sum(M30) as M30,Sum(M31) as M31,Sum(M32) as M32,Sum(M33) as M33,Sum(M34) as M34,Sum(M35) as M35,Sum(M36) as M36,Sum(M37) as M37,Sum(M38) as M38,Sum(M39) as M39,Sum(M40) as M40,Sum(M41) as M41,Sum(M42) as M42,Sum(M43) as M43,Sum(M44) as M44,Sum(M45) as M45,Sum(M46) as M46,Sum(M47) as M47,Sum(M48) as M48,Sum(M49) as M49,Sum(M50) as M50,Sum(M51) as M51,Sum(M52) as M52,Sum(M53) as M53,Sum(M54) as M54,Sum(M55) as M55,Sum(M56) as M56,Sum(M57) as M57,Sum(M58) as M58,Sum(M59) as M59,Sum(M60) as M60,Sum(M61) as M61,Sum(M62) as M62,Sum(M63) as M63,Sum(M64) as M64,Sum(M65) as M65,Sum(M66) as M66,Sum(M67) as M67,Sum(M68) as M68,Sum(M69) as M69,Sum(M70) as M70,Sum(M71) as M71,Sum(M72) as M72,Sum(M73) as M73,Sum(M74) as M74,Sum(M75) as M75,Sum(M76) as M76,Sum(M77) as M77,Sum(M78) as M78,Sum(M79) as M79,Sum(M80) as M80,Sum(M81) as M81,Sum(M82) as M82,Sum(M83) as M83,Sum(M84) as M84,Sum(M85) as M85,Sum(M86) as M86,Sum(M87) as M87,Sum(M88) as M88,Sum(M89) as M89,Sum(M90) as M90,Sum(M91) as M91,Sum(M92) as M92,Sum(M93) as M93,Sum(M94) as M94,Sum(M95) as M95,Sum(M96) as M96,Sum(M97) as M97,Sum(M98) as M98,Sum(M99) as M99,Sum(M100) as M100
from
(
select
LocalNetID, BSCID, SiteID, CellID, StrTime,
M1 , M2 , M3 , M4 , M5 , M6 , M7 , M8 , M9 , M10 ,M11 , M12 , M13 , M14 , M15 , M16 , M17 , M18 , M19 , M20 ,M21 , M22 , M23 , M24 , M25 , M26 , M27 , M28 , M29 , M30 ,M31 , M32 , M33 , M34 , M35 , M36 , M37 , M38 , M39 , M40 ,M41 , M42 , M43 , M44 , M45 , M46 , M47 , M48 , M49 , M50 ,M51 , M52 , M53 , M54 , M55 , M56 , M57 , M58 , M59 , M60 ,M61 , M62 , M63 , M64 , M65 , M66 , M67 , M68 , M69 , M70 ,M71 , M72 , M73 , M74 , M75 , M76 , M77 , M78 , M79 , M80 ,M81 , M82 , M83 , M84 , M85 , M86 , M87 , M88 , M89 , M90 ,M91 , M92 , M93 , M94 , M95 , M96 , M97 , M98 , M99 , M100
from examp
) as T1
group by
LocalNetID, BSCID, SiteID, CellID, StrTime
) as T2
group by
LocalNetID, BSCID, SiteID, CellID, StrTime
) as T3
group by
LocalNetID, BSCID, SiteID, CellID, StrTime
) as T4
group by
LocalNetID, BSCID, SiteID, CellID, StrTime
when there is no data in table, it will take more than 3 minutes on my computer!
how can i optimize the sql to speed it?
thks
It's bad enough that SQL CE 2.0 doesn't support views, when I use "LIKE" in a SELECT statement (eg., SELECT ... WHERE fldname LIKE '%word%'), the response is terrible to the point that it doesn't come back. In my case, the 'word' can be anywhere in 'fldname'.
How do you optimize the LIKE operator? I created an index on the field but it didn't make a bit of difference.
Thank you.
Hello friends,
I’m facing performance related problem while running following query on SQL Server 2000.
This query is basically used to find last location of each unit that are passed. Here I am passing data like “‘26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50'" in @Units variable. But it takes too much time and I don’t get output. Table is having around 5 Million records.
Query:
SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid
FROM tblUnit INNER JOIN
tblUnitHistory ON tblUnit.UnitID = tblUnitHistory.UnitID
WHERE tblUnitHistory.UnitHistoryDate
IN (SELECT MAX(UnitHistoryDate) FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @Units + ') GROUP BY tblUnitHistory.UnitID)
AND tblUnit.UnitID in (' + @Units + ')
ORDER BY tblUnit.UnitID
Table Structure:
UnitHistoryID int Primary Key
UnitID int
Location varchar(200)
Latitude decimal 9
Longitude decimal 9
Speed decimal 5
BatteryVoltage decimal 5
ReasonCode int
DistanceFromLastLocation decimal 9
UnitHistoryDate datetime
Indexes:
1. Clustered Index on Column UnitID
2. Non-clustered Index on Column UnitHistoryDate
3. Non-clustered Index on Column UnitHistoryID
Please help me to write optimize query and suggest me the steps to require making this query faster. Any help is appreciated. Thanks in advance.
Regards,
Sandeep
I have a nightly job that aggregates data into a summary table. Each night, an additional item may or may not be added, so the table is cleared and repopulated. I've got this working fine. Now, I am being asked to track when an item first appears in the summary table. Here is the code I am using to do this. I realize it is very poor performance, but I don't know how to make it better.
MyTable Columns: id INT PK ,v1 VARCHAR(4) ,v2 VARCHAR(10 ,v3 DECIMAL ,item1 BIT ,item2 BIT ,item3 BIT
MyTable2 Columns: v1 VARCHAR(4) ,v2 VARCHAR(10 ,v3 DECIMAL ,item INT ,FirstKnownDate DATETIME
All columns are NOT NULL.1 DECLARE @iNextRowID INT
2 ,@iCurrentRowID INT
3 ,@iLoopControl INT
4 ,@v1 VARCHAR(4)
5 ,@v2 VARCHAR(10)
6 ,@v3 DECIMAL
7
8 /* This is a loop that executes for a dynamic number of items, eg item1=1, then item2=1, etc */
9 SET @iLoopControl=1
10 SELECT @iNextRowID=MIN(id) FROM MyTable WHERE item1=1
11
12 IF ISNULL(@iNextRowID,0) <> 0 BEGIN
13 SELECT @iCurrentRowID = id
14 ,@v1 = v1
15 ,@v2 = v2
16 ,@v3 = v3
17 FROM MyTable
18 WHERE id=@iNextRowID
19
20 WHILE @iLoopControl = 1 BEGIN
21 IF NOT EXISTS(SELECT * FROM MyTable2 WHERE v1=@v2 AND v2=@v2 AND v3=@v3 AND item=1) BEGIN
22 INSERT INTO MyTable2
23 /* COLUMN */ (v1,v2,v3,item,firstknowndate)
24 SELECT @v1, @v2, @v3, 1, GETDATE()
25 END
26
27 SELECT @iNextRowID = NULL
28 SELECT @iNextRowID = MIN(id) FROM MyTable WHERE id>@iCurrentRowID AND item1=1
29
30 IF ISNULL(@iNextRowID,0) = 0 BEGIN
31 BREAK
32 END
33
34 SELECT @iCurrentRowID = id
35 ,@v1 = v1
36 ,@v2 = v2
37 ,@v3 = v3
38 FROM MyTable
39 WHERE id=@iNextRowID
40 END
41 END42 /* This is the end of the items loop */
This query takes 5 minutes to do straight inserts without the test for exists in the loop for 3 items. MyTable has just under 96,000 rows in it. MyTable2 has 121,857 rows in it. It is not about 55 minutes and it is still running with the exists so that a row isn't inserted twice. Is there anything I can do to optimize this? EDIT: The query just finished running again with the test for exists on each row. It shouldn't have inserted any new rows, but it took 57 minutes and inserted another 114,115 rows. Not good. Anyone see what is wrong with the query?
Hello, I have three sql select statments I would like to combine into one. I have created a statment that works but I am not sure if it is a good solution performance wise. Is there a better way to run this query?
Thanks Very Much!!
if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate)
Begin
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate
End
else
if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where nextupdate < GetDate() Order By importance desc)
begin
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where nextupdate < GetDate() Order By importance desc
end
else
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Order By nextupdate
Hi, We have surveys that gather data and we're trying to find the best way to query this data for reporting. It is not too late to change everything so we're open to any suggestions. Here is my current setup:
Results table from which each row represents a user that filled in the survey so it contains a unique identifier, a survey identifier and some other data which we use elsewhere.
Result_Answers table which holds the answers for each result. So it contains the result ID, the question ID and the answer ID
Questions and Answers table which hold the actual text for each identifier used in result_answers.
What we're trying to do is retrieve the count for each answers (thats easy) based on a set of conditional answers. For exemple:
Q1 - Are you male or female
Q2 - What is your age group
Q3 - What type of music do you listen to.
So lets say I want the count of each answers for each question but limited to the results that answered male on Q1 and 18-25 OR 25-35 on Q2. The only way I found to do this is create a cursor to loop trough each questions, select the result IDs that answered Q1 correctly and then remove from that temp table each result ID that didn't answer the subsequent questions correctly. This gives me a page long query with a cursor and multiple IN commands (for when there's more than one answer valid for a single question)
I'm not saying the results are abysmal, I'm still getting my results inside 2s with a bit over 1 million rows in those tables. But unfortunatly, this is basically only one survey... I can only imagine that it will get longer and longer as we add more surveys.
So my question is: Is there a better way to query this data or worst case, is my table schema not efficient enough? In both cases, how can I fix/optimize it?
I'm really hoping you guys can help me out!