.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

slqce query performance

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :ASP.Net
 

I use slqce database to store master-detail table.It took too long to insert byte array data.
Could anyone give me some suggestion on how to improvement the query performance?




View Complete Post


More Related Resource Links

slqce query performance

  
You cannot vote on your own post
I use slqce database to store master-detail table.It took too long to insert byte array data.
Could anyone give me some suggestion on how to improvement the query performance?

Basic Instincts: Increase LINQ Query Performance

  

Jared Parsons demonstrates a technique to achieve maximum performance from LINQ queries on large sets of data in order to create a responsive user interface.

Jared Parsons

MSDN Magazine August 2008


Query Performance & Overall Design - SSAS MDX WCF

  
Hi All, We have a Cube which is to be queried by the Online system , using WCF service. Peroformance of the queries running on the Cube is not really very good , as we have to calculate various percentile (which are all calculated members) on the cube, this howver works well within limits of 5-6 secs for small sample size. But this goes beyond the threshold of 30 secs when the record counts increases. What we have is our SSAS Cubes , and we have WCF Service querying the Cube using ADOMD.NET. This may not be one of the best way to achieve this requirement , but we are kind of completed with development and it may not be feasible to work on another approach, what we are looking for is optimize this design and make it work with in expected time limits of 5-8 secs. Kindly let me know, if i am not clear or if you need any more info to suggest something ! Thanks a lot for your help !   Kindly

Oracle Linked serve Query performance in 2000 vs 2008 R2 64 Bit

  
Hi everyone We have started to migrate one of our reporting systems from Sql 2000 to Sql 2008 R2.  One of the steps has been to test the perforance of certain Oracle linked server queries between each server.  We are finding on average 3 fold better perforance stats (in terms of query completion time) on the old server.  This should obviously not be the case.  The new server has signifantly more CPU/Memory/IO resources to play with, and it is 64 bit (not to mention its new!).  Here's what I got so far: Old server: SQL 2000 on W2000 both fully patched.  Old Dell Dual core with 3 GB of Ram running on two soft IDE Mirrored drives (yes I know... it sucks).  It connecting via the MS OLE DB provider for Oracle (9i client) New server: ESX VMware Server with 2 CPU's assigned, 8 GB of ram connected to large HP SAN.  CPU, ram and IO's have all been ruled out as the problem.  We've tried varying network cards with different results so we havent ruled that out yet.  Its connecting via the Oracle provider for OLE DB (11G 64 bit client) The linked server is an Oracle 9i fully patched server.  All three are on the same network backbone. Running a simple select * query on both servers returns the same number of rows (~76 000) .  It takes ~1:20 on the new server and ~0:20 on the old server. In looking at the wait stati

Full text performance of a certain query

  
I am doing a full text query on a very simple table and when I include the search term "y5v" in the conditions it slows the query down like 8 to 1. The table is defined as: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblTextSrchData]( [intItemId] [int] NOT NULL, [srchTxt] [varchar](max) NOT NULL, CONSTRAINT [PK_tblTextSrchData] PRIMARY KEY CLUSTERED ( [intItemId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Here is the query: SELECT II.intItemID, KEY_TBL.[RANK] as Score FROM tblItems II INNER JOIN CONTAINSTABLE(dbo.tblTextSrchData,srchTxt, '("capacitor" OR FORMSOF(INFLECTIONAL, "capacitor") OR "capacitor*") AND ("active" OR FORMSOF(INFLECTIONAL, "active") OR "active*") AND ("y5v" OR FORMSOF(INFLECTIONAL, "y5v") OR "y5v*")') KEY_TBL ON KEY_TBL.[KEY] = II.intItemID If I take any of the additional search terms out it improves performance.  If I remove the yv5 search term the results are returned in less than a second. What seems to be the problem is for some reason when I add yv5 it starts doing these sorts after the full text matching.  These sorts take most of the time.  I'm not sure

MDX query using linked server - performance question

  
Hi, I'm having a strange performance behavior. When I’m running a mdx query on management studio, it runs for 10 minutes (its mainly calc members on a large cube) When I run the same query via linked server and openquery, it runs for 20 minutes. Is the SQL from some reason does a parsing or running the query twice? The server is sql2008 UP1 on 64bit. and the "allow in process" is checked. its the default in sql2008 Thanks in advance, Yoav  

query w.r.t to SharePoint 2010 Performance Planning & Extranet Topology

  
  1.      Which extranet topology to go with in case of exposing Intranet to Remote Employees / Partners ?   My understanding:   There are 3 extranet topologies : Edge Firewall , Back to Back , Split back to back   If Scenario is : a)      Only Employees needs to access Intranet from Home ,we can go for Edge Firewall b)      If partners & Employees , both needs to access Intranet ( it will have some partner sites ) , then we can go for Back to Back OR Split back to back .   Questions: ·         Is Understanding correct ? ·         Is there any factor which can help in making a decision between Back to Back Or Split back to back ?   2.      As part of sizing , I read that we should capture following information -          What should be maximum acceptable CPU utilization in production servers ? ( Example : 70%) -          What will be the concurrent number of users accessing portal during peak hour of a day ? ( Example 1000 users from total of 18000 users ) -          How many request per second are done to portal

Very slow performance with a query involving JOINS

  
I am experiencing extremely slow performance on a particular SQL query using SQL compact.  This same query completes in less than one second on my regular SQL server.  With SQL compact, I am actually not even sure if the query ever completes.  As I type this I am testing it from SQL management studio, and it's up to 17 minutes and 40 seconds still executing so far. Since I am not experiencing this on most of my other queries, I have little doubt the slowness is due to the volume of data in these tables, coupled with the fact that I am doing the joins.  I made sure that foreign key constraints are in place for the columns that the joins are based on.  From what I've read, this also ensures that appropriate indexes are created, although I am starting to wonder about that. This is the query: SELECT TOP (200) t.BusinessEntityID, t.BusinessName, t.IsTowTruck, t.IsHospital, t.TowTruckLastUsed, t.BusinessLocked, cn.Address1, cn.City, cn.State, cn.PostalCode, cnp.PhoneNumber as TopPhoneNo, GetDate() as LastUpdatedDate, GetDate() as CreatedDate FROM tblBusinessEntity t LEFT JOIN cnBusinessEntityLocation cn ON cn.cnID in (SELECT TOP (1) cnID FROM cnBusinessEntityLocation where cnBusinessEntityLocation.Active = 1 AND cnBusinessEntityLocation.BusinessEntityID = t.BusinessEntityID

MDX query performance is slow for complex logic implementation in Calculated Members.

  
We have implemented a calculated members which involves multiple calculated members  called in a nested e.q. Cal1 calls Cal2 and Cal2 calls  Cal3  and Cal3 calls Cal4 .  And when browing Cal1 through Excel it takes around 30 mins for getting result from a Partition having row count around 5 million. Follwing are the information on the scenario I am having: We are having partitioning in the cube. one partition contains around 4-6 million records Processers on Query Server: 4 quad core RAM on the Query Server: 64 GB Calculated members having simple calculation logics are giving results in no time. There is a Calculated members which calls 6 intermediate calculated in nested way and with small Data Set like 5-6 k rows it is running fine.   Any help will be much appreciated.   Regards, Sandeep

How to calculate a SQL Server performance of a query based upon table schema, table size, and availa

  
Hi What is the best way to calculate (without actual access to a SQL Server) the processing speed of a query (self-inner-join) based upon table schema, table size, and hardware (CPU, RAM, Drives)? ThanksThanks Jeff in Seattle

Query Performance

  
In My View I Have 3 CTE And 3 SubQueris ....It's Taking 5 sec To Load For The First Time And After That 2 sec To Load.View Have 10365 Records.Plz Suggest Me How Can I Make That View To Work Speedy.

CAST in delete query-Performance is so slow

  
Hi all, I've below join in the delete statementt, but it's taking so long the query run, the performance is really bad because of cast conversion, is there a better way, especially performace wise to fasten this below join, A.PHONE in nvarchar data type and DNC.PHONE_NUMBER  is in bigint data type.   A.PHONE=CAST(DNC.PHONE_NUMBER as varchar (15)) Thank you.

Trying to optimize performance of query having Hash Matches & Agreegate

  

Hi,

I am trying to optimize a stored proc which takes about 20 seconds to execute. I tried building the index and replacing the Table scans with Index seeks and the performance came down to 15 sec which is still bad.

In my execution plan 46% goes for Hash Match Aggregate &

10% for HashMatch (JOINS)

12% sort

32% Index seeks.

Is there a way I can optimize Hash Match Aggregates?

 

My stored proc is union of 3 different views. All the views are Joins of 2 different tables(3*2 =6 tables involved). My base tables already have Indexes defined on the column by which I am joining the tables, I am still not able to get Nested Loop Joins, SQL is executing Hash Match Joins.

Do I need to create Index on views as well to achieve the performance.

 

Thanks in advance

-Jayasree


Trying to optimize performance of query having Hash Matches & Agreegate

  

Hi,

I am trying to optimize a stored proc which takes about 20 seconds to execute. I tried building the index and replacing the Table scans with Index seeks and the performance came down to 15 sec which is still bad.

In my execution plan 46% goes for Hash Match Aggregate &

10% for HashMatch (JOINS)

12% sort

32% Index seeks.

Is there a way I can optimize Hash Match Aggregates?

 

My stored proc is union of 3 different views. All the views are Joins of 2 different tables(3*2 =6 tables involved). My base tables already have Indexes defined on the column by which I am joining the tables, I am still not able to get Nested Loop Joins, SQL is executing Hash Match Joins.

Do I need to create Index on views as well to achieve the performance.

 

Thanks in advance

-Jayasree


Order By clause in RowNumber function slowing down the performance of my query.

  

Hi,

I have a query which has a paging functionality and for which i'm using ROW_NUMBER() function. The order by clause in my rownumber function is dynamic and gets changed all the time when ever the sp is called. It works fine for few columns but for one column which is of datetime datatype, it takes lot of time. Amazing thing is with when i sort by the same column in descending order, the query runs in a sec but ascending order takes lot of time :(.

I tried creating a non clustered index(as i already i have a clustered index on that table) on that datetime column but it did not help me.

Could you please suggest what i can do to improve the performance.

Thanks,

Deepti

 

 

 


Performance tuning > two tables with strings > best query to join them

  

I have two subqueries which build a string (varchar(200)), first subquery creates 200000 records and second 50000 records, how can I write a select statement to get records from subquery first with string existing in second subquery records.

Will sorting both the sub query results be helpful? Kindly advice for best performing select statement.


Trying to optimize performance of query having Hash Matches & Agreegate

  

Hi,

I am trying to optimize a stored proc which takes about 20 seconds to execute. I tried building the index and replacing the Table scans with Index seeks and the performance came down to 15 sec which is still bad.

In my execution plan 46% goes for Hash Match Aggregate &

10% for HashMatch (JOINS)

12% sort

32% Index seeks.

Is there a way I can optimize Hash Match Aggregates?

 

My stored proc is union of 3 different views. All the views are Joins of 2 different tables(3*2 =6 tables involved). My base tables already have Indexes defined on the column by which I am joining the tables, I am still not able to get Nested Loop Joins, SQL is executing Hash Match Joins.

Here is my stored proc code. Exec plan is in Excel Spreadsheet form.

https://spreadsheets.google.com/ccc?key=0AtpBDycPp-0_dDZBVHVkdU5QejBmSEE2MGM2bUREQlE&authkey=CN7wqLML&hl=en#gid=0

 

USE [HDIS]
GO
/****** Object: StoredProcedure [dbo].[sp_GetBlockSpecimenInfo] Script Date: 09/23/2010 17:26:11 ******/

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend