I am developing a website with huge data which to be stored in SQL Server database. How should I optimize it to make it faster.
1. Using Stored procedures.
2. Functions / Views.
3. Any other methods
We have a web service method which accepts some data and puts it in Lucene index. We use it to index new and updated entries from our asp.net web app.
These entries are stored in a large SQL Server table (20M rows and growing), and I need a way to be able to reindex the whole table in case if current index gets deleted or corrupted. I'm not sure what's the optimal way to retrieve chunks of data from a large table. Currently, we use the fact that the table has PK which is autoincrement, so we get chunks of 1000 rows until it starts to return nothing. Kind of like (in pseudo language):
i = 0
while (true)
{
SELECT col1, col2, col3 FROM mytable WHERE pk between i and i + 1000
.... if result is empty 20 times in a row, break ....
.... otherwise send result to web service to reindex ....
i = i + 1000
}
This way, we don't need to SELECT COUNT(*) which would be a big performance killer, and we just move up the pk values until we stop getting any results. This has it's con: if we have a hole greater than 20,000 values somewhere in the table, it will stop indexing assuming it reached the end, but that's a tradeoff we have to live for now.
way of getting data from a table to index? I would assume we are not the first ones facing this problem - search engines are widely used nowadays :)
I want to generate 30,000 cards and each card must be duplicate check with database. In my card, there are 2 things. Serial No and CardID. If any card already exists then I generate another card id but with the same serial no.
So how faster way I can generate 30,000 card with duplicate check? Which one I have made application, it takes about 25 minutes to insert.
I am working on a large project where I have to present efficient way for a user to enter data into a form.
Three of the fields of that form require a value from a subset of a common data source (SQL Table). I used JQuery and JQuery UI to build an autocomplete, which posts to a generic HttpHandler.
Internally the handler uses Linq-to-sql to grab the data required from that specific table. The table has about 10 different columns, and the linq expression uses the SqlMethods.Like() to match the single search term on each of those 10 fields.
The problem is that that table contains some 20K rows. The autocomplete works flawlessly, accept the sheer volume of data introduces deleays, in the vicinity of 6 seconds or so (when debugging on my local machine) before it shows up.
The JqueryUI autocomplete has 0 delay, queries on the 3 key, and the result of the post is made in a Facebook style multi-row selectable options. (I almost had to rewrite the autocomplete plugin...).
So the problem is data vs. speed. Any thoughts on how to speed this up? The only two thoughts I had were to cache the data (How/Where?); or use straight up sql data reader for data access?
I have an excel sheet that contains around 30,000 rows and 18 colmns. These no of rows and columns may increase in future. I need to read all these records from excel sheet and insert into a table in sql database. For reading the excel book I am using Oledbconnections. The possible solutions I have known as per my knowledge, to insert the data are
1. To insert one record at a time which makes 30,000 database hits. How will this affect the performance?
2. To use liked servers - bu this is not working for me.I do not have database permissions to use linked servers. So, the only option i have is the first one.
I have a database table that works as a file repository. Currently there are binaries stored in there and I want to pull the "large" ones out in chunks. Some of these files are in excess of 500 MB. I have business rules that dictate if the file is >5MB to transmit in chunks. <5MB and I can load into memory and rip out. I got the uploading in chunks to work, but how do I get it to pull it out of the DB in chunks?
Right now I'm getting hit with a 'System.OutOfMemory' exception. But when I recreate the byte array of the SAME size (empty though) it doesn't break.
Download Chunks (DAL)
public byte[] getBytesByDataID(int chunkSize, string dataID) { string query = "SELECT data.data " +" FROM data " + " WHERE dataID = @dataID"; openConnection(); cmd = new SqlCommand(query, myConnection); cmd.Parameters.AddWithValue("@dataID", dataID);
[code]....
In my web application I am working with files. Some files are very large. I use Response.Write() to write the file to the browser. This goes well for the smaller files, but for large files this can take a while and the bandwidth is fully used.
Is it possible to split large documents and send it piece by piece to the browser? Are there other ways to send the document quicker to the browser?I hold the document as a property of an object.
I want to upload some large files from a web page to MS SQL Sever database, I am very sure that it is sounding weird.
File sizes are around 100MB.
I am having following settings,
SessionTimeOut period = 60 Mins,
Server Operation timeout = 60 Mins,
SQL Connection Timeout = 4 mins (Not sure if this is helping)
This is page is going to be used by our client only once a week & as they are having web farm environment we are avpiding to store these files on file systems.
Currenty we are able to upload files with sizes upto 8 MB succesfully. But when we are uploading a file of 100MB it fails, its for sure that operation takes lots of time.
I am working with VS 2010, Entity framework, SQl-Server 2005, ASP.Net web forms. Currently, I am working on the Data access layer library which soon will be a web service, using Entity Framework collaboration with different design patterns like repository pattern and some best practices that posts in different blogs. I am also test each repository using the Unit testing project. Thumbs up! Working fine.
The thing I am worried about is, how much is good for retrieving data from a table that can contain 80-100k records ?
I am curious to know the following:
We are going to develop a website where we need to store large number of files. The file size may be uptp 50 MB. What approach should we follow:
1) We should store files in Database
2) We should have a directory and store all the files in that
3) We should hire a SAN Storage and use this seperate location to store the files.
I have a local database which is updated regularly. Now I need a good way to reflect the local database changes into the remote sql server. Both the server have same database structure.
All the above pocess should work automatically from the local database server because I need to update the remote one so that the website visitor can get the updated results in the morning.I need a detailed description/ procedure to accomplish the work.
I m trying to save files like pdf, word, excel etc... into sql express, but m getting this error in my web page:An attempt to attach an auto-named database for file C:Usersmalek.safaDocuments est vbSavingFileInDBSavingFileInDBApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
m not getting errors inside my code, just this one appearing on the web page.
when make buakup to my data base and open the database digram this error appear : Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
View 1 Replies how I can correct an error. I've built the MVCMusicStore tutorial here:
http://mike-ciccone.com/Store/
The first time I visit the site and attempt a database connection I get this error:
Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database 'master'.
But when I refresh, it all works fine. I'm at a bit of a loss. I don't believe I'm attempting to create a database, but I realise that the error may not really have anything to do with that. This is hosted at GoDaddy shared hosting. The database was created and I used an SQL script to create the tables initially and populate the data. My connection string works as I can pull data from the database, but I do have
a feeling it will be a web.config setting that will correct this.
i am trying to restore database.bak in sql server 2005 (i know the database.bak was also generated in sql 2005 server)
i am trying to restore back up database .bak into the new database i just created in sql server 2005i have saved my database .bak into c drive and when i select database .bak "From Device", it doesn't get populated in the list below and i see nothing and it keeps on prompting a message"You must select a restore source"
Here's the screen shot: (i tried restoring database in sql server 2008 and it was sucessful but i am facing this problem in sql server 2005 only)
I have some data which is in mySQL, i am not familier with mySQL, i would like to transfer all the data from MySQL to SQL Server database,
View 11 RepliesI have created a web application and an SQL database using Web Developer 2010 Express. I have SQL Server 2008 and SQL Server Management Studio Express. In order to deploy the database to my hosting space I need to create a .BAK file from my new database. I think I can do this from with the Management Studio, but can't figure out how to get it to connect to the database. I have used Management Studio before to retore another SQL database and the only connection I see when I open the Management Studio is the old connection to the old database. How do I connect to my new database?
View 6 RepliesI'm using ASP .NET C# 3.5. I have a multiline textbox on my web form that allows for the input of up to 5,000 characters from the end-user. This text is a basic description of a training course. I need to display it out in a clearly formatted way. For example, I need there to be bullets and bold text.
What I did was I chose certain (not often used) characters and then used the .Replace method when displaying the text in an <asp:Label>. If the text in the database contains the character '~' then I replace that with a line break <br />. If it contains '`' I replace that with <b> and if it contains '^' I replace that with </b>.
Is there a better way of doing this? It is working properly, and I'm displaying the text properly, but I know the end-user is going to hate typing text like this for formatting. I do want this all to stay database driven as well
I have production server on our intranet and we have website on remote server with its own database. Now we want to sync some tables from our local database to remote database at some interval of time. I have own idea that if data sync is fail than we should get email about that from remote database.
For above scenario I was looking some ideas from you guys and different method to do it. I know about replication of database how to do that. Can we do it by asp.net, SSIS package which will be easy.
I'm working with Oracle database and I want to improve performance of my website.I read about caching. So is it possible to implement caching (SQL Server Database Dependency) with oracle?
View 2 Repliesi have database in internet ,its now on my server by using ip and username and password but this database is empty table ;i have database on my pc how can i copy all table from this database to new dabase with relation and procedure..?
View 3 RepliesI am planing to move my sql server 2005 database to sql server 2008 database for my asp.net website. Due do this migration is it I have any issue in sqlserver side or my website will work(sp, views, table) without any issue ?
View 5 Repliesm stuck at one place...i want to tranfer data from one database-1 to another database-2...both databases are for different web application.
Currently i am doing the same using dataset..but the problem is that when Large amount of data is coming in dataset so during the tranfer of data server gives me a Time out Error.
And another thing i am not want to utillise a server memory because there is Lacs of data into the Database for migrate.
am importing a text file database in my sql server database in asp.net c#.but i cannot fetch the column name from .txt file which contains a numeric data now what to do?
View 1 RepliesI have created a sdf database in a mobile application.Now I want to synchronize that database to a sql server database.
View 1 Replies