Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server Connection String

Posted on 2014-02-08
4
Medium Priority
?
428 Views
Last Modified: 2014-02-18
Hi, Experts

I need Fastest sql server 2008 R2 connection string in c#
I am using following connection string which make our website very slow.
connectionString="Data Source=dbserver;Initial Catalog=gpdb;User ID=sa;Password=abcxyz;MultipleActiveResultSets=true;" providerName="System.Data.SqlClient"/>

Open in new window

0
Comment
Question by:Asrar Azmi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39843829
Can you explain why you think the connection string has anything to do with the speed?
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39843847
Try this
	<connectionStrings>
		<add name="conn2" connectionString="Data Source=192.?.?.?\your_db_instance;Initial Catalog=your_db_schema;Integrated Security=False;User ID=your_db_login;Password=??????" providerName="System.Data.SqlClient"/>
	</connectionStrings>

Open in new window


in addition to this, if the website is really slow, it can be because of other reasons, like networking or database. For database, you can try to tune the relevant Sql scripts.
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 1500 total points
ID: 39844254
As hinted by Date Burrell, the connection string has nothing to do with the performance. It's used to connect, but that is all. Once connected, it's the way the database is designed and used that matters.

Do you use text fields instead of integer fields for primary keys and for fields that are used in links? Text fields are not efficient for these purposes.

Do you use indexes where appropriate? They make a big difference in performance.

Do you lock records or run transactions that for long periods of time. If there are too many useless locks, users could be waiting for records to unlock.

Do you store a lot of information in blob fields, such as images. Depending on the application, it is sometimes better to leave images and videos in files and simply store the path in the database.

Do you run the database on a dedicated server. If it runs on a computer that performs a lot of tasks, then the database will be slowed down. You should also be sure to have a decent computer to run the server.
0
 

Author Comment

by:Asrar Azmi
ID: 39850023
I think becoz of this curson it make perfomance slow
can any one can check and please let me know what wrong in the cursor

USE [gpssystem]
GO
/****** Object:  Trigger [dbo].[CheckGeofenceArea]    Script Date: 02/11/2014 16:17:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[CheckGeofenceArea] ON [dbo].[gpsobject] 
FOR UPDATE
AS

If UPDATE (latitude) AND UPDATE(longitude)

declare  @devicenum varchar(50),@vehiclename varchar(50),@dynlatitude decimal(18, 4),@dynlongitude decimal(18, 4),@systimetime datetime ,@speed as decimal
declare @id int,@radius decimal(18, 4),@fixlatitude decimal(18, 4),@fixlongitude decimal(18, 4),@rowno int
---------------------------------------------------------------
select @devicenum=devicenum,
@dynlatitude=latitude,
@dynlongitude=longitude,
@systimetime=systime,
@speed=speed
from inserted

--select @rowno =(select count(*) from alerts where devicenum =@devicenum )

--select @id=id, @radius=radius, @fixlatitude=latitude , @fixlongitude=longitude  from  alerts where devicenum=@devicenum and isactive=1 order by id
-------------------------------------------------------------
Declare MY_data CURSOR LOCAL FOR
 --359231039842834
--Select Name ,Mobile from  My_table (Nolock)
select id,radius,latitude,longitude from  alerts where devicenum=@devicenum and isactive=1 order by id 

OPEN MY_data
    FETCH NEXT FROM MY_data INTO @id ,@radius,@fixlatitude,@fixlongitude
        WHILE @@FETCH_STATUS = 0
        BEGIN
 
           ------------------------------------------------------------------------------- 
                      --Validate Function goes here.
                      declare @result varchar(3)
                     
                      exec @result = dbo.ValidateLocation @fixlatitude,@fixlongitude,@radius,@dynlatitude,@dynlongitude
					  --exec ValidateLocation1 'Out', 24.8034,46.7457,2,24.1569,47.3387
					  update Alerts set AreaStatus=@result,systime=@systimetime,speed=@speed where id=@id
           -------------------------------------------------------------------------------
            
            
	
		
			
		
        FETCH NEXT FROM MY_data INTO @id ,@radius,@fixlatitude,@fixlongitude
        END
    CLOSE MY_data
DEALLOCATE MY_data


----------------------------------------------------------------


--update Alerts set AreaStatus='Out',systime='" + sysdatetime + "',speed='" + speed + "' where id='" + strid + "' and devicenum=@devicenum;


	PRINT 'AFTER UPDATE Trigger fired. : CHECK_GEOFENCE_AREA'

Open in new window

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Compliance and data security require steps be taken to prevent unauthorized users from copying data.  Here's one method to prevent data theft via USB drives (and writable optical media).
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question