Making XML In SQL Server Faster - Convert To Tables?
Feb 22, 2012
I have a field in the database that is XML because it represents a class that is used in C#/VB.Net. Problem is after the initial manipulation most, but not all, of themanipulation is done in SQL Server. This means that the XML field is converted on the fly.As we are getting more fields and more records, this operation is getting slow. I think the slow down is the converting all of those fields to other data types.So to speed it up I was thinking of a couple of ways:Have a set of tables that represent the different pieces of the XML data. I would make these tables read only using a trigger on Insert/Update that would reject any changes. My 'main' table with the XML in it when it updates the XML would turn off the triggers, update the tables with the new values then turn the triggers back on.
The only real reason we use the XML is because it's really easy to convert it to the class in C#/VB.Net. But I'm getting the point where I may end up writing a routine that will take all the bits and pieces and convert it to a class and also a function to go the other way (class -> tables).
I have made picture viewer in VB which also shows all the pics present in a folder as thumbnails using image list and listbox. The problem is whenever the user clicks refresh it takes long time for the image to load. I want to some how make a cache like thing where i can store the image for faster access in the future.
For a touchscreen oriented application, i have an onscreen keyboard that can operated by both the hardware keyboard, or via touchscreen/mouse. The user needs this keyboard a lot, but. Because it has so much buttons, it loads really slow and takes about a second to finally display.
My Onscreen keyboard is of type dialog, and its background is set to the transparency key. Once the form is there it works really well. the problem is the 1 second loading time, this can be pretty frustrating when you need to edit like 20 things. Is there any way to preload the keyboard dialog and make it popup instantly?
Does anyone know of a fast way in VB to go from a string to a generic type T constrained to a valuetype (Of T as Structure), when I know that T will always be some number type?This is too slow for my taste:
But it seems to be the only sane method of getting from a String --> T. I've tried using Reflector to see how Convert.ChangeType works, and while I can convert from the String to a given number type via a hacked-up version of that code, I have no idea how to jam that type back into T so it can be returned.
I'll add that part of the speed penalty I'm seeing (in a timing loop) is because the return value is getting assigned to a Nullable(Of T) value. If I strongly-type my class for a specific number type (i.e., UInt16), then I can vastly increase the performance, but then the class would need to be duplicated for each numeric type that I use.
It'd almost be nice if there was converter to/from T while working on it in a generic method/class. Maybe there is and I'm oblivious to its existence?
Conclusion:Testing the three provided implementations below and my original DirectCast/ChangeType form, @peenut's approach of using a prepared delegate to fetch the Parse method from a basic type works. No error checking is done, however, so implementors need to remember to only use this with valuetypes that have a Parse method available. Or extend the below to do error checking.
All runs were done on a 32bit system running Windows Server 2003 R2 with 4GB of RAM. Each "run" is 1,000,000 executions (ops) of the method to be tested, timed with StopWatch and reported back in milliseconds.
Original DirectCast(Convert.ChangeType(myStr, GetType(T)), T):
1000000 ops: 597ms Average of 1000000 ops over 10 runs: 472ms Average of 1000000 ops over 10 runs: 458ms
[code]....
Using System.Reflection and calling InvokeMethod to get at the Parse method:
1000000 ops: 12213ms Average of 1000000 ops over 10 runs: 11468ms Average of 1000000 ops over 10 runs: 11509ms Average of 1000000 ops over 10 runs: 11524ms
[code]....
Konrad's approach to generate IL code to access the Parse method and store the call into a delegate:
1000000 ops: 352ms Average of 1000000 ops over 10 runs: 316ms Average of 1000000 ops over 10 runs: 315ms
[code]....
peenut's approach of using a delegate to access the Parse method directly:
1000000 ops: 272ms Average of 1000000 ops over 10 runs: 272ms Average of 1000000 ops over 10 runs: 275ms
[code]....
Comparatively, peenut's approach is almost 200ms faster when executed 1,000,000 times in a tight loop, so his approach wins out. Although, Konrad's wasn't far behind and is itself a fascinating study of things like ILGenerator.
i was just wondering how i would do this task. It seems fairly simple but i dont no how to to do it. In my system i have a table where i have a total figure of the stock. what i am trying to do is when my saler order takes 5 i want it to project this in my overal amount. How would i do this. I am not asking for codes just a method of doing it
I have to convert a set of C# classes (class library) to SQL tables to be used by SQL Server, so that the data can be stored in a database and manipulated through the database.The problem is that the number of these classes is big (more than 1000 classes),and it would take a long time to setup manually such a database schema (tables,ndexes, stored procedures, etc.) - not to mention the class hierarchies that I need to maintain.
I am trying to learn OOD/OOP and read on the net that I am not supposed convert SQL tables into classes. But I cannot find an explanation why not to do it or which tables to skip. So I made an web application based on the below diagram. It works perfectly well. But I do not use OO. which table should not be mapped into a class and why not. do not use LINQ or NHibernate, etc as I do not understand them. I just need to understand the OOD.
I am not sure if the above title is suitable for what I mean, but basically my application has a login table with entry U_ID and I have also created another field in my other table which will store this ID from the login table.
What I want to do is, if a user with say U_ID = 1 downloads data and stores it in the second table I would also for trackable reasons be able to store the U_ID of that user in the second table so that I would know who downloaded the information.
How can I acheive this and if there are any example then point to them.
I have one application create tables in database dynamically during run. Another application need to find all these tables' name in the database. how should in do in vb.net
I have a select query like this. But from the below codes, I cannot update t2.unit_price in the same SqlDataAdapter
My Codes : Dim MyQuery1 as String="Select t1.item_id,t1,group_id,t2.unit_price from Table1 t1 left outer join Table2 t2 on T1.Item_ID=T2.Item_ID" Dim DAp1 as SqlDataAdapter,Ds1 as New DataSet Con.Open() DataAdapter1=New SqlDataAdapter(MyQuery1,Con) DataAdapter1.Fill(Ds1) [Code] .....
From the above codes Dim Sav1 As String = "insert into table1 .........." I don't know how to mention table2 also there ?
I have an ASP.NET project which is a front-end to a database. In addition to the large tables, the DB contains a few small tables to help normalize the larger tables with common values. I have a VB.NET project which loads the smaller tables into memory, using "Shared" (i.e., "static" in C#) member variables, and uses them. I have a call to load the tables in Global.asax - Application_Start. This works for a while. That is, Application_Start runs when I first run my project, loads the cached values, and will correctly keep them in memory for a while.
What I'm seeing (when running my project via Visual Studio 2008 Debugger, hosted locally) is:
A) The Application_Start code will run more than once. Not in a row, but after the user has navigated to some other pages, I'll see (my breakpoint in) another call to initialize the cache, coming form Application_Start. Is it expected?
B) The "Shared" variable that was set to True when the cache was initialized is now False again (which should only happen when the class is first loaded). Similarly, all the data that was chached is no longer present. That is, it looks like VB is unloading all the Shared members. Is this expected?
I have about 4 different tables with the exact same column names. What I would like to do is select the top 4 records out of all of these tables combined ordered by date (as date is one of the columns that they all share).
I have Memberships and Bookings tables in database containing an attribute cust_id which is primary key in Memberships and reference key in Bookings. When i am executing data reader I want it to read cust_id values from membership table but it is reading it from the bookings table.....
I have an off-line project. User uses a laptop to input data off line into local SQL server express. Once connected to domain (on line), local data will sync with remote SQL server using sync framework. (It is done, works fine)One job left:Since another user used another program to update some tables in remote SQL server, such as CUSTOMER, SHIPINFO, my off-line program need to update these new data once connect to domain.Here is what I design in Pseudo (take CUSTOMER as example)
1) In remote SQL server, create a store procedure (sp_customer) to select all CUSTOMERS
2) In application, create a dataset (dst_customer) based on sp_customer
3) Insert all customers data which is in remote SQL server but not in local SQL server based on CUSTOMER_ID
I'm making a program that would require me to send data between to different machines.The way i plan to do this is to have a server program constantly running on my computer.(any other way to do this?) When someone opens the Client program, the program connects to the server. The server then takes the users I.P address and Username and adds it to a listbox on the Server application. Then the server application constantly updates the list of users on the Client application. So that way every user can see everyone that is connected.Then, when the user wants to connect to another user - They select the Username from their listbox of users. Then all connections will be handled between the users = Each user is connected to the other user's Client program. Well there is the first thing i don't know how to do.The next is simply how i send the data between the users. Would i use a winsock control?Example: One user does an action. The winsock control sends the text "blue" to the other user. The other user's program's form color changes to the color blue. That's not what this program will do, but it's just an example.
So im making a text replacer for PSP Programmers to easily convert the codes to NitePR format. None of you know what that is but w/e Heres my problem I load the text file that has the code into a text box.
Then i press the button to convert. It has this code
i have been trying to make a program that should restart my server if it crashes. Just some few problems... it won't work.[code]....My problem is that when it notices the program runs, it won't do the process again, and just stop watching for the server crash.
Customer | Product ------------------ A | Car A | Bike A | Boat
[Code].....
If I use a normal JOIN then I get a list of friends for every separate product. I just want the two lists once.
The output lists with '-' need not be table cells, they can be <ul>s.
How can I achieve this? I would like to bind to ASP.net GridView. Should I try to do it all in one query, or use multiple queries and somehow add them both to the same Grid?
Each row actually forms part of a long report. Essentially each row of the report contains Customer ID, a bunch of other fields which match one-to-one with Customer ID, then the two lists for each Customer ID I described. Perhaps I can use a separate query for each list, then manually add each list to the grid on RowDataBound or similar?
im makign a game that reads text files off of an ftp server. everything is working great. except for the fact that i cant read more then the first line of the players text file. how would i use substring to, for example, look for 'inventory' and get all the info under 'inventory' and above the next title?
I have some questions about building reports in Report builder 3.0 OR SQL Server BI Studio 2008. I use the report builder for the good wizard. Make better reports dan de BI wizard. And after that i work further in the BI Studio. Now i have a problem i have a report(letter) that i need to generate. But i exists of 2 pages. The first page is a letter with the second page as Attachment with some calculations. So i may not printed on the same page. But i can't make 2 page's in de report builder or BI studio. How can i do that? Because i must place on each page a footer with some general info.
What is the best way to convert an int (bit value from SQL Server) to a Boolean value in vb.net? I am currently using Cbool, but believe this is now obsolete.
In VS 2008, VB.Net, I am successful connecting to a local SQL Server (version 8) and seeing all tables/views/procedures in Server Explorer. The local OS is Win XP Pro SP3. I created an ODBC data source to a remote SQL server (also version 8) with a database of the same structure as the local one. The connection tests out ok. The remote computer runs Windows Server 2000 SP4. In the VS 2008 Server Explorer I add this ODBC source. When I click on the plus-signs to expand items, neither the tables nor the views show anything, whereas the full list of procedures appears. Is it the old SQL Server version? Is it the old Server OS version? Something else alltogether?
In VS 2008, VB.Net, I am successful connecting to a local SQL Server and seeing all tables/views/procedures.I created an ODBC data source to a remote SQL server with a database of the same structure as the local one.The connection tests out ok. In the VS 2008 Server Explorer I add this source. When I click on the plus-signs to expand items, neither the tables nor the views show anything, whereas the full list of procedures appears.How can I see the lists of tables and views in Server Explorer for an ODBC source?
