How Do I Make 30 Sec Running Query (select C1 Sum(x) From T1 Where C1 &&> 1000 Group By C1) Run Faster?
Aug 10, 2007
It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.
Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?
if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?
Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?
Thanks in for you help in advance
View 1 Replies
ADVERTISEMENT
Mar 20, 2007
I need help makeing the following query run more efficently.
Code:
SELECT t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
FROM Landlord_tbl t1
left outer join Mail_tbl t2
ON t2.potentialSitesID = t1.potentialSitesID
WHERE t2.mailed_out_date is null and NOT(t1.firstName+t1.lastName) is Null
GROUP BY t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
ORDER BY t1.firstName, t1.lastName, t1.city, t1.state
View 3 Replies
View Related
Oct 3, 2006
Any help would be really appreciated....
My stored procedure...
CREATE PROCEDURE business3rd7
@Fromdate DATETIME,
@ToDate DATETIME
AS
select distinct CONVERT(VARCHAR(10),Receipts.Companynumber1)+CONVE RT(VARCHAR(10),Receipts.Companynumber2) as co ,
Receipts.Premium1+Receipts.Premium2 as Premium,
"CAN"=case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else
0 end,
-------
"$NEW"=
case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else
0 end,
"$RENEW"=
case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0
end,
"$AP"=
case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else
0 end,
"#SENT"=case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) end,
"%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end =0 then 0
when case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) else 0 end=0 then 0
else
case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end /case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) else 0 end end,
"Current Year"= case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(clubamount) end,
"Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @Fromdate)
AND DateAdd(year, -1, @ToDate) then count(clubamount) end,
"#AA"=count(receipts.clubamount),
"$AA"=sum(receipts.clubamount)
FROM Receipts,Policy
where Receipts.Agencyid=Policy.Agentid
group by
Receipts.CompanyNumber1,Receipts.CompanyNumber2,
Receipts.Premium1,Receipts.Premium2,
Receipts.TransactionType,policy.Renewalofferdate,
Receipts.Agencyid
GO
Query plan...
----------------------Query Plan
|--Sort(DISTINCT ORDER BY:([Expr1008] ASC, [Expr1009] ASC, [Expr1010] ASC, [Expr1011] ASC, [Expr1012] ASC, [Expr1013] ASC, [Expr1014] ASC, [Expr1015] ASC, [Expr1016] ASC, [Expr1017] ASC, [Expr1018] ASC, [Expr1019] ASC, [Expr1020] ASC, [Expr1021] ASC, [Ex
|--Compute Scalar(DEFINE:([Expr1008]=Convert([Receipts].[CompanyNumber1])+Convert([Receipts].[CompanyNumber2]), [Expr1009]=[Receipts].[Premium1]+[Receipts].[Premium2], [Expr1010]=If ([Receipts].[TransactionType]='CAN') then ([Receipts].[Premium1]+[R
|--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1076]), [Expr1005]=Convert([Expr1077]), [Expr1006]=Convert([Expr1078]), [Expr1007]=If ([Expr1078]=0) then NULL else [Expr1079]))
|--Stream Aggregate(GROUP BY:([Receipts].[CompanyNumber1], [Receipts].[CompanyNumber2], [Receipts].[Premium1], [Receipts].[Premium2], [Receipts].[TransactionType], [Policy].[RenewalOfferDate], [Receipts].[AgencyID]) DEFINE:([Expr1076]=COUN
|--Sort(ORDER BY:([Receipts].[CompanyNumber1] ASC, [Receipts].[CompanyNumber2] ASC, [Receipts].[Premium1] ASC, [Receipts].[Premium2] ASC, [Receipts].[TransactionType] ASC, [Policy].[RenewalOfferDate] ASC, [Receipts].[AgencyID] ASC))
|--Hash Match(Inner Join, HASH:([Policy].[AgentID])=([Receipts].[AgencyID]), RESIDUAL:([Policy].[AgentID]=[Receipts].[AgencyID]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Policy]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Receipts]))
The two tables has number of records as 13349 and 97032.It taking more than 30 mins...
Any way to make it faster...
View 14 Replies
View Related
Feb 1, 2008
Let's say I have a table of users.
Let's imagine there's two fields:
username (PK),
password
Now I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to
(1) SELECT * FROM [User]
WHERE username = 'whatever' AND password='whatever'
or
(2) SELECT * FROM [User]
WHERE username = 'whatever'
and then in my code check that the record returned matched the password?
View 6 Replies
View Related
Jun 5, 2015
I want to make a calculation to have the same return as the following SQL Query:
SELECT MAX(COLLECT_TOTAL)
FROM [dbo].[FACT_CAPACITY_COLLECT]
GROUP BY CI_NAME, FILESYSTEM_ID, SUBTYPE_ID, Month_DAY
How Can I do it?
View 2 Replies
View Related
Apr 17, 2008
I recently installed SQL2005 Express on a Dell Precision workstation to be accessed by 6 users. The PC has XP Pro, 2GB memory, and 2.0GHz core2due CPU. This PC is dedicated for the database only.
I also set XP Performance options in System Properties to Background services and System cache.
Now, the users are experiencing slow response from the database.
How can I make the database response faster?
Thanks for your input.
View 4 Replies
View Related
Jan 26, 2001
Hi.We have stored procedure update specific table
Each time it run it delete 5000- 6000 rows
from table then insert 5000- 6000 rows with different information.
It take up to 1 1/2 min execute.
1.Can force Sql server do not make entry for each insert and if yes would it increase speed of procedure ?
2. Is any other way increase speed of insert?
View 2 Replies
View Related
Nov 29, 2007
Hi:
I have thousands of rows that I have to retrieve for my report. When I run my main report it returns my data as a reasonable time.
When I run my sub report (by its own) again data are returned in reasonable time.
When I build the sub report in my main report it takes forever (I have to stop). Are there any tricks that I can use to make this run faster?
Thank you in advance
Bahram
View 3 Replies
View Related
Aug 5, 2007
Hi,
Here is the typical processing I do in Data Flow
1) read data file (data source)
2) do something with the data (e.g. derived column, script component, etc)
3) pass data as parameters into OLE DB command (stored proc) to take care of the rest.
Would it be faster if
I split the data into say 10 "chunks" (using split transform). Each chunk will go to its own OLE DB command (10 OLE DB command). Now I will have 10 concurrent processes running the stored proc ?
Am i misunderstanding concurrent process here ?
Thanks
View 1 Replies
View Related
Jun 17, 2004
Anyone got any alternatives to the SQL Statement below that would make it run a bit faster:
ALTER PROCEDURE sproc_ReturnAvailability
@ExtractDate DateTime,
@DateFrom DateTime,
@DateTo DateTime,
@96hrPlusFlag int,
@AppointmentsCount int OUTPUT
AS
IF @96hrPlusFlag = 0
BEGIN
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @ExtractDate
AND tbl_SurgerySlot.StartTime BETWEEN @DateFrom AND @DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
ELSE
BEGIN
IF @96hrPlusFlag = 1
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @ExtractDate
AND tbl_SurgerySlot.StartTime >@DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
Cheers...
View 1 Replies
View Related
Sep 3, 2007
Any ideas how to make reports faster when returning lots fo rows?
I know you would need to work on your sql query etc..
Or maybe cache it.
But i'm thinking of having a kind of middle tier thing that sits between your sql database and the reports itself.
Any ideas would be appreciated
View 2 Replies
View Related
Jan 11, 2006
Dear VB coders!
I have 8.000 or 16.000 products on my list in an sql table. It takes 8 or 16 secs to find the last item.
How could I make the search faster? Indexing? But how?
I created:
createTable1.CommandText = "Create TABLE products (ID int IDENTITY(0,1) PRIMARY KEY, name ntext)"
and I use simple WHERE to search.
Thank you!
View 3 Replies
View Related
Oct 24, 2001
Is it safe to backup while the database is running 1000 transaction/sec.? If yes - why should I buy Veritas Backup Exec Server Edition and Veritas Backup Exec Online Backup Pack ?
Michael
View 2 Replies
View Related
Jul 12, 2014
I have a Managers table in my SQL DB and it has a ManagerID, MgrName and MgrPhoto field base...
I can code a form with a select button that displays a drop down with the managers in it as choices, but am a little confused as to how I would make the PHOTO (MgrPhoto) change to the corresponding ManagerID that is selected from that option drop down since the SELECT CODE in the form only queries the ManagerID / MgrName combo for the choice.. The photo is below that drop down box and how to make it change to whatever photo is assigned to the selected ManagerID / MgrName ?
Here is the form code with query :
<form enctype="multipart/form-data" action="updatemanagerphoto.php" method="POST">
<select name="ManagerID" id="manager" style="width:400px" class="form_textbox">
<?
$db_connect= mysql_connect($db_host, $db_username, $db_password);
mysql_select_db($db_name, $db_connect) || die(mysql_error());
$sql_query= "SELECT * FROM Managers ORDER BY MgrName ASC";
[code]....
View 2 Replies
View Related
Mar 7, 2007
Dear All,
i am making small web application using asp.net, C# ,sql2000.
i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.that is how to make select query to access view in other server Please help
thanks
View 1 Replies
View Related
Jun 20, 2008
Hello All
I have table with 3 columns and Datas like this
LRNo LRAmount LRType
L001 100 TP
L002 250 PA
L003 150 AC
L004 275 TP
L005 400 PA
L006 125 TP
I need a query to get output like this
LRtype Count TotalAmount
TP 3 500
PA 2 650
AC 1 150
Where count is total no of records grouped for LR Type and totalAmount is Sum of TotalAmount grouped by LRType
please help me
Thanks in Advance
View 4 Replies
View Related
Nov 14, 2005
Hi All,
I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.
I have been able to create the resluts I want using UNION, but this is not practical when there is more than a few groups. Below is the code the returns the results I am after, any ideas how to achieve this another way?
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value],
COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Earthquake'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Windstorm'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Tornado/Hail'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Flood'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
ORDER BY LookupPeril.PERILNAME, [Total Value]
TIA.
View 4 Replies
View Related
Nov 16, 2005
Hi All,
I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.
I have been able to create the results I want using UNION, but this is not practical when there is more than a few groups. I think it should be possible using a derived table, but I am not sure how to do it! Below is the code the returns the results I am after, any ideas how to achieve this another way?
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value],
COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Earthquake'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Windstorm'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Tornado/Hail'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Flood'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
ORDER BY LookupPeril.PERILNAME, [Total Value]
TIA.
View 3 Replies
View Related
Jun 20, 2006
Hi
I need some help in returning the top 3 records in each group ie I have a table containing a list of stores with the address details and I need to return the top 3 records in each postcode area. The Postcode field contains the postcode area ie BN1, BN2 etc. Many thanks.
View 3 Replies
View Related
Aug 27, 2007
I have a SELECT TOP query in order to return x number of top records from a table which has the indexing service enabled on it, such as this :
SELECT TOP(15) * FROM [TableName] ORDER BY [ColumnName]
and also there are not that many records(MAX 100 rows) kept in the table at the moment however, it will grow later.
The issue stems out from the ORDER BY [ColumnName] part of the syntax that changes the TOP selection order which makes the query to run very slow as I have also analyzed in the SQL SERVER QUERY ANALYZER.
Anyhow, I need to have the ORDER BY statement to show the data based on different columns either ascending or descending.
There might we workarounds to achieve the same goal that I am not aware of.
Any thoughts is appreciated.
View 3 Replies
View Related
May 12, 2015
I am using SQL SERVER 2008R2, not Denali, so I cannot use OFFSET FETCH Clause.
In my stored procedure, I am doing a SELECT INTO #tblTemp FROM... Working fine. This resultset is going to be used in an SSIS package which will generate a pipe-delimited .txt file... Working fine.
For recoverability sake, I am trying to throttle back on the commit chunks to 1000 rows per commit until there are no more rows. I am trying to avoid large rollbacks.
Q: Am I supposed to handle the transactions (begin/commit/rollback/end trans) when the records are being inserted into the temp table? Or when they are being selected form the temp table?
Q: Or can I handle this in my SSIS package for a flat file destination? I don't see option for a flat file destination like I do for an OLE DB Destination (like Rows per batch, Maximum insert commit size).
View 6 Replies
View Related
Sep 26, 2006
HelloI need this really faster in mS SQL 2000Usernumber (int)reportid (FK)reportreportid (PK)Category (int)SELECT A, B, C, D INTO UserCopy FROM UserWHERE User.reportid IN (SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Category) AND user.number NOT IN (120,144,206,345,221,789,548,666,1204,4875,22,135, 777,444)can return a more than 1000 rows (an the table = 10.000 rows): SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Categoryand the table user has a few millions rowsReport.ReportId is a Primary key for User.reportid (FK) for the moment it takes up to 3 minutes, i need to do that in 30 seconds maximumthank you for helping
View 14 Replies
View Related
Feb 26, 2014
I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.
View 3 Replies
View Related
Aug 11, 2007
Hi,
Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
WHERE FREETEXT(*, 'foo')
GROUP BY Username
ORDER BY [Count] DESC
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct
ON OptionID = ct.[KEY]
GROUP BY Username
ORDER BY [Count] DESC
They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.
But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.
If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).
So... I am worrying about this unnecessarily? Should I just use the simpler first version?
Any thoughts appreciated.
Thanks
View 3 Replies
View Related
Feb 13, 2008
I'm reporting from a Microsoft SQL database (poorly documented unfortunately) and would like to find a 3rd party application to assist me in rapidly making Select queries. The ability to browse data in a field from the interface would be a plus.
What are the best alternatives for rapidly creating these queries from some sort of builder or wizard?
TIA.
View 7 Replies
View Related
May 8, 2015
How to write a query that can insert over 1000,0000 dummy records in the fastest way? My current query is
DECLARE @d DateTIme = GETDATE()
DECLARE @c INT = 1
WHILE @c <= 5 BEGIN
INSERT INTO MyTable VALUES (NEWID(),'PREFIX' + RIGHT('0000000000'+ (CAST(@c AS VARCHAR)), 10), DATEADD(MINUTE,@c,@d), FLOOR(RAND()*3), FLOOR(RAND()*2), 'INFO')
SET @c = @c + 1
END
View 7 Replies
View Related
Nov 7, 2003
Hello,
Lets say I have a SP that return 1000 records,
do I get any better speed if doing it on a SP instead of just SELECT without an SP ?
if I have many users on a web-site that will execute this SP - will they get any better
speed because it is a SP ? - can SP cache itself - if so - for how long ?
(Why should I use SP if not passing any parameters ?)
View 7 Replies
View Related
Jul 23, 2005
Hi.We need to create a view of our active directory users (we have 2500).I found out that there is max page size of 1000, so we cannot get moredata.Anyone found a solution to that problem?Thanks
View 1 Replies
View Related
Oct 1, 2007
I have a query that has the following structure
Select *
From Table
Where Condition And ... (some 'Exists' conditions)
When I run the query using field names the query gets much slower, and I cannot understand Why!
Select MyField
From Table
Where Condition And ... (some 'Exists' conditions)
I'm talking about three times slower using the Select MyField sintax.
Any ideas???
View 8 Replies
View Related
Jun 20, 2007
Hi y'all,
I've recently run a profiler on my code and following query took 7 seconds:
SELECT TOP 10 UI, COUNT(UI) AS Expr1 FROM table WHERE (UI <> 'custom_welcome') GROUP BY UI ORDER BY COUNT(UI) DESC
Is it possible to rewrite this so my code gets faster? It's also possible that it's due to the size of the table?
Thanks in advance! I'll let you know how long your query takes :)
View 4 Replies
View Related
Mar 31, 2004
Hi!
I M basically an application developer & use simple sql queries in my programmings. I do not have much idea abt tuning/auditing part & thatswhy i m unable to answer them properly in my interviews. Can anybody give me some tips?????
Question 1:
In a stored procedure, One SELECT stmt is there & depending upon the @rowcount, it updates around 14000 records which is also written inside this stored procedure. Instead of writing this way, there is some other way which is faster than this. Can anybody tell me the correct way???
Question 2:Can anybody give me few examples like this?????? I need them desparetly.
Thanx. Bye.
View 37 Replies
View Related
Jul 26, 2006
I have rewritten a stored procedure that consists of a single select that selects from a view. Essentially I combined the select in the view and the select in the sp into one select. I am now trying to determine if the new version is faster.
The estimated execution plan gives a ratio of 96% : 4% in favour of the new version when I run them together from a query window but when I try to time them I can't get a satisfactory result.
If I run each query once and display the difference between start and end time, they display 0. If I run each one 100, 200, etc times I get different results each time.
Is there anything I can do?
View 5 Replies
View Related
Feb 11, 2015
I have a sql snippet from a 3rd party application that will not complete its transaction. The SELECT statement executes but does not finish. Instead the statement just sits in AWAITING COMMAND for 1000 seconds then dies, thus killing the UPDATE statement that is supposed to follow.
The CROSS JOIN and CROSS APPLY seem suspect.
(
@p0 DATETIME,
@p1 INT,
@p2 INT,
@p3 NVARCHAR(4000),
@p4 INT,
[code]....
View 9 Replies
View Related
