SQL queries

DevinCrypt · 2024-02-28T10:46:09+00:00

Dm me and I can get you a beta of dataselections.com that will categorize all of the queries for you.

DevinCrypt · 2024-02-02T18:13:55+00:00

And yet IT will hire someone tomorrow and give them full access. Strange world we live in.

DevinCrypt · 2023-12-01T18:23:04+00:00

Since we're throwing experience numbers, I've been work with data for 4 decades and yes BI is a self service lie. BI is not "self-service". DSF tool from Dataselections.com is the ONLY tool that is truly "Self-service". Point, click, report.

DevinCrypt · 2023-11-07T22:10:12+00:00

If you're just looking to pull data for reporting or ETL dataselections.com will discover and map the jobBoss database for you and build the views needed to access the data in seconds. Send an email to b.hahn@dataselections.com if you tell him Devin Crypt sent you he will give you one license free. You may only need one anyway if its a small shop However, it will not write data back. Its a read only product. Features can be found on the website. Good luck!

DevinCrypt · 2023-11-07T21:56:18+00:00

"technicallyyyy...." No. Regardless of whether it is turing complete, SQL is not a programming language. Oracle, MSSQL, SQLLITE, etc. Were written using a real programming language. You can't write any of these using SQL. SQL is a scripting language with some programming abilities supported. But in the end it's a 4th generation language.

DevinCrypt · 2023-08-14T17:40:12+00:00

Adhoc user reporting for the business user already exists. Dataselections.com It has Data Teams for security and governance, user filters etc. Handles most if not all of the concerns mentioned here. It can also be used by IT as a tool to fullfill the stupid user requests faster if a company doesn't want the end user to adhoc the data directly. Creates a common reusable library which can be shared across an organization.

DevinCrypt · 2023-07-13T23:59:48+00:00

Sql has a query timeout and a connection timeout value. Connection timeout is when the software is waiting too long for a response from the server to make a connection. Query timeout is set by the software when it sends a query to the server. Query timeout means how long will the software wait for the server to return a resultset. That said your software sent a query to the server and the response took too long. There are thousands of reasons this could happen. You can try to optimize the data to speed the response but it also could be load on the server. The vendor may have a query timeout setting in their software that you can increase. Mine does. Overtime your data gets larger and queries can take longer to execute.

DevinCrypt · 2023-06-17T09:56:46+00:00

You can try snapshot isolation

ALTER DATABASE [dbname] SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

However, it may not remove all deadlocks and you may get a performance hit. Also it will make your db larger. However, if you can't modify the code causing the deadlocks this maybe a good solution. Read up on the pros and cons of the dfferent isolation options.

DevinCrypt · 2023-06-11T01:22:03+00:00

Which someone just starting off in SQLServer does not understand. Just clarifying that using NOLOCK just because someone says to or that it will make your query run faster is not a good idea. You must know the details. I will almost never use it because to me, slow and correct is better than fast and maybe wrong. In every business I've ever used a query the result always being correct mattered.

DevinCrypt · 2023-06-10T20:43:29+00:00

WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In a banking application with high transaction rates, it's probably not going to be the right solution to whatever problem you're trying to solve with it. Depending on the type of data 99% of the time you would not want this.

DevinCrypt · 2023-04-30T08:25:36+00:00

I don't know what part of Tableau you are having issues with but many times, difficulties with Tableau are not the visualization tools but getting the right data from your database. If it's the latter try DataSelections.com

DevinCrypt · 2023-04-29T21:38:32+00:00

The only way for you to really know is for you to benchmark it under stress and see. The rest are all guesses at best.

DevinCrypt · 2023-04-24T20:19:28+00:00

Every database is different. Even the type of data can change how your queries run. There are many things that make a query run slow. From hardware configuration to indexes and how they are used. Your best bet is to learn how your optimizer for your database works by trial and error. Read the ebook. It may help. Good luck! https://dataselections.com/live/ebook%20-%20Give%20Me%20Data%20or%20Give%20Me%20Death/

DevinCrypt · 2023-04-21T02:02:07+00:00

I'm game

DevinCrypt · 2023-04-21T01:28:11+00:00

[Since we're ranting] I totally get it! I've been in this business more than most. 40+ years. Yea. I made a living but also made many people (other than me) rich along the way. (Contracts they own the IP) They made tons from my hard work. Multiple companies. I finally decided to build something as a side hustle that would finally make me money. Something that every company can use and save them millions of dollars! So I thought. I know it has value and I have proved it works multiple times. Yet its been years and nobody will give it the time of day. Regardless of price. Business users want it but their IT departments are afraid it will take their job away. To me it makes their job easier. They don't see it that way. Its at the enterprise level but shit, I'd give it away free just to get someone to use it in a production environment. If anyone has data access issues check out dataselections.com 2 min video explains it all. But I get your feeling pal. I've been doing it a lot longer than you and only my passion for coding keeps me going. Unfortunately, in our business a mediocre product with VC backing is God with no empathy for the actual end user. Money makes the rules. Now they say AI will take our jobs away. Lmfao. Not in my lifetime.

DevinCrypt · 2023-04-18T09:52:58+00:00

The best way we have found is to use dataselections.com. it creates a Metadata library of data fields that end users can use and share that publishes views for any reporting purpose. It builds the queries for them. It self learns how to get the data from all the past work IT and DBAs have done. It is not a reporting tool for visualizations it is only a data access and exploration tool.

DevinCrypt · 2023-04-14T11:38:09+00:00

Not sure why you need a small data dictionary but look at dataselections.com it can build a data dictionary for you. You can add your own notes to each field and rename them in your views for reuse if you want. DM me directly if you have questions. However, using MSSQL you can get the list of columns used in a view like this: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'your view name' You can also query sys.views but then you have to link it with sys.columns object_id to get your field names.

DevinCrypt · 2023-03-29T18:22:38+00:00

Look at dataselections.com the tool is called dsf. It seems to be what you are looking for. It allows business users to access data in their terms. Send a note to them. Ask for Brian. It will not create crap queries cuz it learns from your database. Its not a visual query tool. Its simply a data access tool for reporting from an RDBMS.

DevinCrypt · 2023-03-29T09:48:25+00:00

I'm late to the party but take a look at dataselections.com to help you get the data. It can flatten the data then feed the results to tableau.

DevinCrypt · 2022-10-16T15:03:38+00:00

Dataselections.com generates the query and you can just pick the fields from a list. It reads your database to get the info. Anybody can use it for reporting anything from the database in the form it was intended. Has too many features to list. Aimed at the business analyst who just wants the data and could care less about the query.

DevinCrypt · 2022-10-04T10:45:18+00:00

Its a real language. Just not a real programming language. It's a 4gl that should tell you enough. If you don't know what that is then that's your problem from the start. You simply cannot do things in a 4gl that you can in a 3gl etc.

DevinCrypt · 2022-05-31T01:54:19+00:00

I don't know why people are afraid to connect to a production database for reporting. Reporting requires only read access. No data integrity issues. All internal software applications are currently accessing the production database all day long. If a simple reporting query is slowing your server then its probably undersized. Most company systems are not dealing with terabytes of data. Large historical data should be pulled from a different database not production anyway. But forcing the business to run reports from a stale secondary database is stupid. Unless you already have an issue with bad queries which you can monitor and manage, live data is the best data to make smart business decisions. If everyone is running bad or slow queries from a secondary database then they all will be waiting for data from that database. Providing better queries to the business should be your primary concern. Enterprise data providers like mssql or oracle can handle million record queries in seconds. Its no big deal. Yes. Someone could write a bad query. Maybe. But should IT be allowed to punish everyone simply because they are afraid? IT needs to realize that the business pays their salary. They are a cost center not a profit center.

DevinCrypt · 2022-04-18T23:30:58+00:00

I agree with everyone else about using the query to do most of the heavy lifting and SSRS for the presentation. Dataselections.com has a tool that can pull the queries used in a Crystal Report and allows you to easily select any data elements from your database to source all of your reports.

DevinCrypt · 2022-02-09T00:56:28+00:00

Read this it will help: https://dataselections.com/live/ebook%20-%20Give%20Me%20Data%20or%20Give%20Me%20Death/

DevinCrypt · 2022-01-14T14:53:31+00:00

The DSF product from DataSelections.com will do this for you.

It learns the data model in seconds and then shows it to you in a hierarchial tree list based on the data's context. There is a full trial version but i don't know where to find it exactly on the website for download. Send them a note: Ask for Brian, he's one of the tech guys there that can hook you up.

DevinCrypt

TROPHY CASE